CASE and NULL

CASE expression is a very useful when we need to branch our output in a query. However, we should always to include a
ELSE subcondition in the branch because SQL server assumes that a "ELSE NULL" is included if the ELSE subcondition is
not given explicity. This may not be the expected bahaviour in your context.
E.g.:
DECLARE @Filtering nvarchar(1000)
DECLARE @SQL nvarchar(2000)
SET @Filtering=’ AND id<40′
SET @SQL=N’
SELECT * FROM dbo.sysobjects WHERE name like ”sysc%”’+CASE WHEN ISNULL(@Filtering,N”)<>N” THEN @Filtering END
PRINT @SQL
EXEC(@SQL)
GO
Run the query, you get the right results. If you change @Filtering to empty string, you will get nothing:
DECLARE @Filtering nvarchar(1000)
DECLARE @SQL nvarchar(2000)
SET @Filtering=”
SET @SQL=N’
SELECT * FROM dbo.sysobjects WHERE name like ”sysc%”’+CASE WHEN ISNULL(@Filtering,N”)<>N” THEN @Filtering END
PRINT @SQL
EXEC(@SQL)
GO
This is because the result of the CASE expression is NULL, which leads to NULL for @SQL becuase NULL plus any string
results NULL too. But this is not the end of the story.
DECLARE @Filtering nvarchar(1000)
DECLARE @OrderBy nvarchar(100)
DECLARE @SQL nvarchar(2000)
SET @Filtering=”
SET @OrderBy=’ ORDER BY [id]’
SET @SQL=N’
SELECT * FROM dbo.sysobjects WHERE name like ”sysc%”’+CASE WHEN ISNULL(@Filtering,N”)<>N” THEN @Filtering END
PRINT @SQL+@OrderBy
EXEC(@SQL+@OrderBy)
GO
Run the above query, you get the following error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword ‘ORDER’.
The PRINT statement does not print anything because @SQL is NULL, and @SQL+@OrderBy is NULL too
However, when you execute the query, SQL treats NULL like empty string. This makes the error difficult to find.
Another example is CASE in a query:
DECLARE @Agg int
SET @Agg=1
SELECT id, CASE WHEN @Agg=1 THEN COUNT(*)
  WHEN @Agg=2 THEN MAX(colid)
 END AS Agg
FROM dbo.syscolumns
GROUP BY id
GO
Run the query, you get the number of columns for each table. If you change @Agg to any value not listed in the
subcondition of the CASE expression, you will get NULL for each table, e.g.:
DECLARE @Agg int
SET @Agg=0
SELECT id, CASE WHEN @Agg=1 THEN COUNT(*)
  WHEN @Agg=2 THEN MAX(colid)
 END AS Agg
FROM dbo.syscolumns
GROUP BY id
GO
So as a best practice, always include a "ELSE" subcondtion in the CASE expression.
Advertisements
This entry was posted in SQL server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s