Home > T-SQL > T-SQL CASE Statement checking for NULL

T-SQL CASE Statement checking for NULL

July 17th, 2011

I have found after much annoyance, that an SQL query I had in production was incorrectly checking for NULL in my SQL statement.

This is a simple school-boy error that I will now never do again.

The issue is that if you have a case statement as follows:

CASE WHEN <Value to check> = NULL
    THEN <Value to replace NULL>
    ELSE <Value that is not null>
END

This will always return NULL when there is a NULL value that is in the value check.

However, all you need to do is change the ‘=’ to an ‘IS’ and it will calculate correctly

CASE WHEN <Value to check> IS NULL
    THEN <Value to replace NULL>
    ELSE <Value that is not null>
END
Categories: T-SQL Tags: , , , , , , , ,
  1. September 24th, 2012 at 02:32 | #1

    Very great post. I just stumbled upon your blog and wanted to say that I
    have really enjoyed browsing your weblog posts. After all I’ll be subscribing in your rss feed and I hope you write again soon!

  2. Rich
    November 7th, 2012 at 00:06 | #2

    Thanks! Great Info! Rich

  3. January 20th, 2013 at 13:49 | #3

    This particular post, “T-SQL CASE Statement checking for NULL” illustrates the fact that u comprehend precisely what you’re speaking about! I personally fully am in agreement. Many thanks -Pete

  4. July 17th, 2013 at 09:45 | #4

    I love your blog.. very nice colors & theme. Did you design this website yourself or did you hire someone to do
    it for you? Plz answer back as I’m looking to create my own blog and would like to find out where u got this from. thanks

  5. June 7th, 2014 at 00:30 | #5

    Excellent post. I absolutely appreciate this site. Thanks!

Comments are closed.
%d bloggers like this: