Search code examples
sqlsql-serverisnull

SQL Server : ISNULL not returning a value

I'm trying to write a SQL Statement (in Microsoft SQL Server) that will always return a value of 0 if there is nothing to be found.

SELECT 
    ISNULL([ID], 0) AS ID 
FROM 
    ecdb.dbo.tbl_units 
WHERE 
    ([GENESYS_NAME] = 'EDRD');

The statement above returns an empty value (nothing), see screenshot.

Now if I enter a true statement that returns a value (replace 'EDRD' with 'ERD'), it returns a value of 1, which is correct.

The reason why I need it to return a value of zero is that I plan to use this statement as a sub query of a master statement where I need the ID (not the GENESYS_NAME) and my table has set aside 0 to be an unknown entry.

Any help?

enter image description here

Solution

  • If you are expecting only 1 row as result you can use an aggregate function like MAX():

    SELECT ISNULL(MAX([ID]), 0) AS ID 
FROM ecdb.dbo.tbl_units 
WHERE ([GENESYS_NAME]='EDRD');

    Another way to do it, which will work also if you expect more than 1 rows in the results is with UNION ALL:

    SELECT [ID]
FROM ecdb.dbo.tbl_units 
WHERE ([GENESYS_NAME]='EDRD')
UNION ALL
SELECT 0
WHERE NOT EXISTS (
  SELECT 1 FROM ecdb.dbo.tbl_units 
  WHERE ([GENESYS_NAME]='EDRD') 
)