Geek Answers Handbook

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

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

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

select isnull(@var, 0);

The above code outputs 0if if Column1is null. Or if the string is not found

While I tried to save a few keystrokes, but this led to the fact that

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

The above code outputs nullif Column1null or when there is no line

Any ideas what is wrong here? Or is there a shorter way to write the first code?

+4
source share
2 answers

You can do

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

, .

+4

, - : -)

SELECT TOP 1 tbl.field
FROM
(
    SELECT 0 AS inx, 'no record' AS field
    --if only one row is possible, than set '1' literally
    UNION SELECT ROW_NUMBER() OVER(ORDER BY mytable.orderfield), ISNULL(mytable.Land,'is null')
    FROM mytable
    WHERE IDENTITY = @suppliedID
    ) AS tbl
ORDER BY tbl.inx DESC
+1

More articles:


All Articles