SQL: IS NULL not working

Question

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'.

Answer 1

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)