• Post Categories

  • Browse Blogs

  • Blog Stats

    • 450,649 hits
  • Syndications

    SQLServerPedia Contributor

The CASE of the missing non-NULL T-SQL Error: None of the result expressions in a CASE specification can be NULL.

Recently, while doing some data scrubbing for a customer I got an interesting error in SSMS with one of my CASE statements. The error received was:

None of the result expressions in a CASE specification can be NULL.

It was a long T-SQL script (and a long night) and could not understand the error message. After a couple minutes BinGling (Google +Bing) around the web, I still could not find the root cause, so I decided to comment out every single CASE statement and run one by one until I pinpointed the offending syntax.

 

To my surprise, the issue was very simple, yet undocumented. The T-SQL syntax I was writing was somewhat as follows:

 

SELECT ProductID,

CASE WHEN SerialNumber like ‘X%’ THEN NULL

WHEN SerialNumber = ‘0’ THEN NULL

WHEN SerialNumber = ‘-‘ THEN NULL

WHEN SerialNumber = ‘Unknown’ THEN NULL

END AS SerialNumber_Clean

FROM tblProduct

 

After executing this script the error mentioned above is raised. The same error is raised even if we rewrite the script as a simple CASE statement:

 

SELECT ProductID,

CASE SerialNumber

WHEN ‘0’ THEN NULL

WHEN  ‘-‘ THEN NULL

WHEN ‘Unknown’ THEN NULL

END AS SerialNumber_Clean

FROM tblProduct

 

So what if we add an ELSE statement as follows:

 

SELECT ProductID,

CASE WHEN SerialNumber like ‘X%’ THEN NULL

WHEN SerialNumber = ‘0’ THEN NULL

WHEN SerialNumber = ‘-‘ THEN NULL

WHEN SerialNumber = ‘Unknown’ THEN NULL

ELSE NULL

END AS SerialNumber_Clean

FROM tblProduct

 

We still get the same error. The issue as the raised error describes can be narrowed down in that you cannot explicitly return NULL for every resulting expression. There must be at least one non-explicit NUL in the resulting expression. For example, we can rewrite the script correctly as follows:

 

SELECT ProductID,

CASE WHEN SerialNumber like ‘X%’ THEN NULL

WHEN SerialNumber = ‘0’ THEN NULL

WHEN SerialNumber = ‘-‘ THEN NULL

WHEN SerialNumber = ‘Unknown’ THEN NULL

ELSE SerialNumber

END AS SerialNumber_Clean

FROM tblProduct

 

As you can observe, adding an ELSE statement that does not return another explicit NULL makes the script work and as a matter of fact, should be the correct syntax. Interestingly, ELSE is not necessary to make the script valid. The script will run even without an ELSE expression, but only if at least one of the resulting values is not an explicit NULL. The following script runs successfully (notice no ELSE):

 

SELECT ProductID,

CASE WHEN SerialNumber like ‘X%’ THEN NULL

WHEN SerialNumber = ‘0’ THEN NULL

WHEN SerialNumber = ‘-‘ THEN NULL

WHEN SerialNumber = ‘Unknown’  THEN NULL

WHEN SerialNumber = ‘No Serial’ THEN ‘Non-Serialized Product’

END AS SerialNumber_Clean

FROM tblProduct

 

Books on Line should include this caveat on their documentation. http://msdn.microsoft.com/en-us/library/ms181765.aspx

 

The following sections should be modified as follows:

THEN result_expression

Is the expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE. result expression is any valid expression. If no else_result_expression is specified or if else_result_expression is set to return an explicit NULL, at least one result_expression has to be specified to return a non-explicit NULL.

 

ELSE else_result_expression

Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression is any valid expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion and at least one of them should be specified to return a non-explicit NULL.

4 Responses

  1. Excellent find, definitely should be documented better.

    Just to be contrary, and in case anyone REALLY wants to have a null in their CASE expressions, this works:

    SELECT ProductID,
    CASE WHEN SerialNumber like ‘X%’ THEN NULL
    WHEN SerialNumber = ‘0’ THEN NULL
    WHEN SerialNumber = ‘-‘ THEN NULL
    WHEN SerialNumber = ‘Unknown’ THEN NULL
    ELSE NULLIF(SerialNumber,SerialNumber)
    END AS SerialNumber_Clean
    FROM tblProduct

  2. Great article but surely it’s GooBing (Google +Bing)?

  3. Hi
    I just came across this and it is quite interesting. I did find (running SQL 2005) that you can use


    ELSE (SELECT NULL)

    and that works too.

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

%d bloggers like this: