CREATE BLOG dbo.SqlServer

The other day I got a question from a former student about a strange CASE statement error message she was getting: "None of the result expressions in a CASE specification can be NULL". She was kind enough to post her code and, sure enough, when I tested it, I got the same error. Now I've been around SQL Server for a long time but I've never seen SQL Server throw error 8133 so I started playing around... First - let's have a look at the code:

USE tempdb
GO
CREATE TABLE MyTable (EmpId INT NOT NULL PRIMARY KEY, EmpName VARCHAR(128))
GO
INSERT MyTable VALUES (1, 'Scott Whigham')
INSERT MyTable VALUES (2, 'Aimee Wilson')

SELECT EmpId, CASE WHEN EmpName = 'Scott Whigham' THEN NULL END AS TheName FROM MyTable

When (or "if") you try to run this code, you too will get error 8133, "None of the result expressions in a CASE specification can be NULL". If you know anything about SQL Server's case statements, you know that to be a lie - and we can easily prove it:

SELECT EmpId, CASE WHEN EmpName = 'Scott Whigham' THEN NULL ELSE EmpName END AS TheName FROM MyTable

Simply by adding the ELSE clause into the statement we get the query to run without an error - therefore, as far as I can tell, the error text for 8133 is misleading at best.

Here's a funny twist on it: if you reverse the logic of the original, offending statement, it works great:

SELECT EmpId, CASE WHEN EmpName <> 'Scott Whigham' THEN EmpName END AS TheName FROM MyTable

Although the syntax is different, this query is the same as the first query listed above yet it works fine. If you aren't aware of the logic of the CASE operator, because there is no ELSE clause in the above query, then it returns NULL for any EmpName = 'Scott Whigham' (which was what the first query asked for explicitly).

I can't find anything on the web about this error that gives me a reason for it showing up here - anyone have any ideas?