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.
