Case Statement

Evaluates a list of conditions and returns one of multiple possible result expressions.

The CASE expression has two formats:

·         The simple CASE expression compares an expression to a set of simple expressions to determine the result.

·         The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Both formats support an optional ELSE argument.

CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select list, IN, WHERE, ORDER BY, and HAVING.

Simple CASE expression: 

CASE input_expression 

     WHEN when_expression THEN result_expression [ ...n ] 

     [ ELSE else_result_expression ] 

END 

Searched CASE expression:

CASE

     WHEN Boolean_expression THEN result_expression [ ...n ] 

     [ ELSE else_result_expression ] 

END

Arguments:

input_expression

Is the expression evaluated when the simple CASE format is used. 

Input_expression is any valid expression.

When when_expression

Is a simple expression to which input_expression is compared when the simple CASE format is used. 

When_expression is any valid expression. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.

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.

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.

WHEN Boolean_expression

Is the Boolean expression evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.

Return Types:

Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.

Simple CASE expression:

The simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.

·         Allows only an equality check.

·         Evaluates input_expression, and then in the order specified, evaluates input_expression = when_expression for each WHEN clause.

·         Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.

·         If no input_expression = when_expression evaluates to TRUE, the SQL Server Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

Searched CASE expression:

·         Evaluates, in the order specified, Boolean_expression for each WHEN clause.

·         Returns result_expression of the first Boolean_expression that evaluates to TRUE.

·         If no Boolean_expression evaluates to TRUE, the Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

Remarks:

·         SQL Server allows for only 10 levels of nesting in CASE expressions.

·         The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.

·         The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied. In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input. Errors in evaluating these expressions are possible. Aggregate expressions that appear in WHEN arguments to a CASE statement are evaluated first, then provided to the CASE statement. For example, the following query produces a divide by zero error when producing the value of the MAX aggregate. This occurs prior to evaluating the CASE expression.

WITH Data (value) AS

(

SELECT 0

UNION ALL

SELECT 1

)

SELECT

   CASE

      WHEN MIN(value) <= 0 THEN 0

      WHEN MAX(1/value) >= 100 THEN 1

   END

FROM Data ;

You should only depend on order of evaluation of the WHEN conditions for scalar expressions (including non-correlated sub-queries that return scalars), not for aggregate expressions.