I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL Server: Writing CASE expressions properly when NULLs are involved

We’ve all written a CASE expression (yes, it’s an expression and not a statement) or two every now and then. But did you know there are actually 2 formats you can write the CASE expression in? This actually bit me when I was trying to add some new functionality to an old stored procedure. In some rare cases the stored procedure just didn’t work correctly. After a quick look it turned out to be a CASE expression problem when dealing with NULLS.

In the first format we make simple “equals to” comparisons to a value:

SELECT CASE <value>
WHEN <equals this value> THEN <return this>

WHEN <equals this value> THEN <return this>
-- ... more WHEN's here
ELSE <return this>
END


Second format is much more flexible since it allows for complex conditions. USE THIS ONE!

SELECT  CASE
WHEN <value> <compared to> <value> THEN <return this>
WHEN <value> <compared to> <value> THEN <return this>
-- ... more WHEN's here
ELSE <return this>
END

Now that we know both formats and you know which to use (the second one if that hasn’t been clear enough) here’s an example how the first format WILL make your evaluation logic WRONG.

Run the following code for different values of @i. Just comment out any 2 out of 3 “SELECT @i =” statements.

DECLARE @i INT
SELECT  @i = -1 -- first result
SELECT  @i = 55 -- second result
SELECT  @i = NULL -- third result



SELECT @i AS OriginalValue,

-- first CASE format. DON'T USE THIS!
CASE @i
WHEN -1 THEN '-1'
WHEN NULL THEN 'We have a NULL!'
ELSE 'We landed in ELSE'
END AS DontUseThisCaseFormatValue,

-- second CASE format. USE THIS!
CASE
WHEN @i = -1 THEN '-1'
WHEN @i IS NULL THEN 'We have a NULL!'
ELSE 'We landed in ELSE'
END AS UseThisCaseFormatValue


When the value of @i is –1 everything works as expected, since both formats go into the –1 WHEN branch.

CaseResult1

When the value of @i is 55 everything again works as expected, since both formats go into the ELSE branch.

CaseResult2

When the value of @i is NULL the problems become evident. The first format doesn’t go into the WHEN NULL branch because it makes an equality comparison between two NULLs.
Because a NULL is an unknown value: NULL = NULL is false. That is why the first format goes into the ELSE Branch but the second format correctly handles the proper IS NULL comparison.

CaseResult3

 

Please use the second more explicit format. Your future self will be very grateful to you when he doesn’t have to discover these kinds of bugs.

Print | posted on Monday, March 18, 2013 9:55 PM | Filed Under [ SQL Server Back to Basics ]

Feedback

Gravatar

# re: SQL Server: Writing CASE expressions properly when NULLs are involved

Very informative.

Thanks.
3/19/2013 1:36 PM | John Maher
Gravatar

# re: SQL Server: Writing CASE expressions properly when NULLs are involved

It always comes back to NULLs, doesn't it?
3/19/2013 5:17 PM | Matt Velic
Gravatar

# re: SQL Server: Writing CASE expressions properly when NULLs are involved

The first format gets correct result only if you set:
SET ANSI_NULLS OFF
3/23/2013 5:51 PM | Marcel Miklovic
Gravatar

# re: SQL Server: Writing CASE expressions properly when NULLs are involved

The "WHEN {value} {compared to} {value} THEN" is slightly misleading - it's actually "WHEN {condition} THEN". The {condition} can be any valid boolean expression.
3/25/2013 9:13 PM | Richard
Gravatar

# re: SQL Server: Writing CASE expressions properly when NULLs are involved

Great article! Thanks!
3/26/2013 9:36 AM | David Rymell
Gravatar

# re: SQL Server: Writing CASE expressions properly when NULLs are involved

In regards to format 1 vs. format 2;

The danger with format 2 is that every test (WHEN) must be mutually exclusive and it is the programmers responsibility to ensure that they are mutually exclusive. I've run into more than one "bug" where non-exclusive conditions led to inappropriate execution (the first true condition is executed, then the CASE statement exits).

You can also use ISNULL() to handle your NULLs.
3/26/2013 3:28 PM | Marc Jellinek
Gravatar

# re: SQL Server: Writing CASE expressions properly when NULLs are involved

The two formats are tools and as stated it is up to the programmer to know which tool to use and how to use it.

You don't use a hammer to drive screws do you?

SELECT @i AS OriginalValue,
CASE ISNULL(@i,0)
WHEN -1 THEN '-1'
WHEN 0 THEN 'We have a NULL!'
ELSE 'We landed in ELSE'
END AS DontUseThisCaseFormatValue
3/28/2013 7:44 PM | B T
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET