CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
user2096512
user2096512

Reputation: 469

SQL: IS NULL not working

I'm trying to test if a variable is null but I seem to have a syntax error, my code is:

DECLARE @count1 INT

SET @count1 = 
(SELECT HPlayedHome FROM League WHERE GameID = 
(SELECT TOP 1 GameID From Goals WHERE Date <= '20130923' AND Home = 'Palermo'
AND (GameID >=2551 AND GameId <= 5100) ORDER BY Date Desc))

SELECT CASE @count1 IS NULL THEN 0 ELSE @count1 END

I want the query to return the value of @count1 but if it's NULL return 0. SQL SERVER 2012 is highlighting a syntax error on IS, 'Incorrect syntax near IS'.

Upvotes: 2

Views: 16565

Answers (2)

Bohemian
Bohemian

Reputation: 425348

There are two forms of CASE:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE result3
END

CASE variable
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE result3
END

You could use form 2, but change the IS to WHEN to make it syntactically correct:

SELECT CASE @count1 WHEN NULL THEN 0 ELSE @count1 END

Unfortunately, the comparison is made using an equality test, and NULL does not equal NULL, so you must use form 1:

SELECT CASE WHEN @count1 IS NULL THEN 0 ELSE @count1 END

There is already a function to deal with nulls: COALESCE(), which returns the first non-null value passed to it:

SELECT COALESCE(@count1, 0)

Upvotes: 1

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56727

CASE WHEN is correct syntax:

SELECT CASE WHEN @count1 IS NULL THEN 0 ELSE @count1 END

You are aware of the fact that you can shorten this to

SELECT ISNULL(@count1, 0)

Upvotes: 11

Related Questions