|
|
Error Message
Server: Msg 8133, Level 16, State 0, Line 1
None of the result expressions in a CASE specification
can be NULL.
Causes
The CASE function evaluates a list of conditions and returns one of multiple possible result expressions. The CASE function has two formats, namely, the simple CASE format and the searched CASE format.
|
The simple CASE format compares an expression to a set of simple expressions to determine the result. The searched CASE format evaluates a set of Boolean expressions to determine the result. Both of these formats support an optional ELSE argument. The data type of the returned value of the CASE function is the one with the highest precedence type from the set of types in the list of result expressions and the optional ELSE result expression.
The syntax of the simple CASE format is as follows:
CASE <input_expression>
WHEN <when_expression> THEN <result_expression>
[ ...n]
[ ELSE <else_result_expression> ]
END
The syntax of the searched CASE format is as follows:
CASE WHEN <Boolean_expression> THEN <result_expression>
[ ...n]
[ ELSE <else_result_expression> ]
END
The <input_expression> is any valid expression to be evaluated. The WHEN <when_expression> is any simple valid SQL Server expression to which the <input_expression> is compared against. The data types of the <input_expression> and each <when_expression> must be the same or can be implicitly converted. The THEN <result_expression> is the expression returned when <input_expression> equals <when_expression> evaluates to true or when <Boolean_expression> evaluates to true. The ELSE <else_result_expression> is the expression returned if no comparison operation evaluates to true. If the ELSE <else_result_expression> is not specified and no comparison operation evaluates to true, then the CASE function will return a value of NULL. Lastly, the <Boolean_epxression> is any valid Boolean expression that is evaluated when the searched CASE format is used.
As mentioned earlier, the data type of the returned value of the CASE function is the one with the highest precedence type from the set of types in the list of result expressions and the optional ELSE result expression. If the data type of the returned value cannot be determined, then this error message is generated. To illustrate, here’s a SELECT statement that will generate this error message:
CREATE TABLE [dbo].[Student] (
[StudentID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[FirstName] VARCHAR(100),
[LastName] VARCHAR(100),
[Gender] CHAR(1)
)
GO
SELECT [StudentID], [FirstName], [LastName],
CASE WHEN [Gender] NOT IN ('M', 'F') THEN NULL END
FROM [dbo].[Student]
Msg 8133, Level 16, State 1, Line 2
None of the result expressions in a CASE specification can be NULL.
The SELECT statement tries to return NULL as the gender of a student if it is neither M (for male) nor F (for female) but the CASE statement will also return NULL if the gender is either M or F since nothing is specified what to return if it fails the WHEN condition.
Solution / Work Around:
There are a few ways in overcoming this error message. One method is to provide a return value in the ELSE argument as follows:
SELECT [StudentID], [FirstName], [LastName],
CASE WHEN [Gender] NOT IN ('M', 'F') THEN NULL ELSE [Gender] END
FROM [dbo].[Student]
Alternatively, the sequence of the conditions can be rearranged and still get the same result:
SELECT [StudentID], [FirstName], [LastName],
CASE WHEN [Gender] IN ('M', 'F') THEN [Gender] ELSE NULL END
FROM [dbo].[Student]
Another method is by setting the data type of the NULL return value by using the CAST function:
SELECT [StudentID], [FirstName], [LastName],
CASE WHEN [Gender] NOT IN ('M', 'F') THEN CAST(NULL AS CHAR(1)) END
FROM [dbo].[Student]
Regardless of which of the methods shown above is used, the important thing is to be able to determine the data type of the return value of the CASE function, whether using the simple CASE or the searched CASE format. The error message is actually misleading as a NULL value can be used as the result expression in a CASE function as shown by the example.
|