Tuesday, June 7, 2011

SQL Error: None of the result expressions in a CASE specification can be NULL

I got this error when my CASE statement in SQL returned NULL and did not have any ELSE section in the CASE statement: "None of the result expressions in a CASE specification can be NULL"

The CASE statement which resulted in error was like this:

SELECT 
CASE WHEN StudentId = 0 THEN null END

I wanted to return NULL when StudentId = 0; else return the StudentId.

The above query did not work without the ELSE part. After adding the ELSE section like below the query executed without error:

SELECT 
CASE WHEN StudentId = 0 THEN null ELSE StudentId END