If the value returns the value. If a record does not exist or the column is null, return 0 in Sql Server - different ways

advertisements

I want to return 0 if there is no record or if the Column1 is null.

select @var =  Column1
from myschema.mytable
where Id = @suppliedId;

select isnull(@var, 0);

The above code outputs 0 if if Column1 is null. Or if a row is not found

Whereas I tried to save some keystrokes but it resulted in,

select isnull(Column1, 0)
from myschema.mytable
where Id = @suppliedId;

The above code outputs null if Column1 is null or when there is no row

Any ideas what is wrong here ? Or is there any shorter way of writing the first code ?


You can do

SELECT @var = ISNULL(MAX(Column1), 0)
FROM   myschema.mytable
WHERE  Id = @suppliedId;

A scalar aggregate always returns a single row even if the underlying query returns zero rows.