comparison operator in count() is not working

• satishpatro

  Valued Member

  Points: 64

  More actions

  November 14, 2019 at 4:07 am

  Go to Answer

  #3697550

  SELECT 15 > 14 FROM Duel;
  

  Working in MySql but not in SqlServer

  SQL Error(102): Incorrect syntax near '>'

  I have a requirement where I want to see if at least 1 record exist return 1 or else 0

  SELECT count(emp.name) > 0
  FROM ****
  WHERE *** IN (***)

  Check the db fiddle https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=348709a562ad02cbe88abf01a37229f1

• DesNorton

  SSC-Insane

  Points: 24399

  More actions

  November 14, 2019 at 4:34 am

  #3697552

  IF (SELECT count(emp.name) FROM **** WHERE *** IN (***)) > 0
  BEGIN
    SELECT 1;
  END;
  ELSE
  BEGIN
    SELECT 0;
  END;

  ---

  How to post data/code on a forum to get the best help.

• satishpatro

  Valued Member

  Points: 64

  More actions

  November 14, 2019 at 4:59 am

  Answer

  #3697555

  I ended up using IIF

  SELECT IIF(count(erm.employee_code) > 0, 1, 0)
  FROM ****
  WHERE *** IN (***)

• SQLian

  SSC-Addicted

  Points: 413

  More actions

  November 18, 2019 at 10:55 am

  #3698462

  If you just want to know if there are any such records it is usually better to use "exists" rather than making SQL Server provide a full count

  IF exists (SELECT null FROM **** WHERE *** IN (***))
  BEGIN
    SELECT 1;
  END;
  ELSE
  BEGIN
    SELECT 0;
  END;