CASE (TSQL Function)

Returns the first result in the list for which the condition is true.

Two syntaxes can be used:

Simple CASE syntax
Searched CASE syntax

Simple CASE syntax is used when an expression must be compared for equality with a list of values.

Searched CASE syntax is used for more complex conditions.

Syntax

Simple CASE syntax:

CASE expression
  WHEN value_1 THEN result_1 [...WHEN value_n THEN result_n]
  [ELSE result_else]
END

Searched CASE syntax:

CASE
  WHEN condition_1 THEN result_1 [...WHEN condition_n THEN result_n]
  [ELSE result_else]
END

Arguments

expression: an expression of any type, which will be compared for equality with value_1…value_n.
value_1…value_n: is a list of expressions of any type.
condition_1…condition_n: are condition expressions of type bool.
result_1…result_n: is a list of expressions of any type. From left to right, if a condition is true, the corresponding result will be returned.
result_else: optional clause. If no condition is true, this result is returned. It can be of any type.

Return Types

Returns the type of the result with the highest type precedence.

Remarks

If ELSE clause doesn’t exist and no condition is true, NULL is returned.

The simple CASE syntax is internally rewritten as a searched CASE syntax:

CASE a WHEN c1 THEN r1 WHEN c2 THEN r2 ELSE r3 END            is internally rewritten as:

CASE WHEN a=c1 THEN r1 WHEN a=c2 THEN r2 ELSE r3 END

This means that the expression a is evaluated multiple times.
So, you should not use non-deterministic functions such as RAND() within CASE, as explained for NULLIF.
MS SQL Server has the same issue.

Examples

DECLARE @a INT = 2;

-- Simple CASE syntax
PRINT CASE @a WHEN 1 THEN 'Apples' WHEN 2 THEN 'Oranges' WHEN 3 THEN 'Bananas' ELSE 'Other fruit' END;

-- Searched CASE syntax
PRINT CASE WHEN @a=1 THEN 'Apples' WHEN @a=2 THEN 'Oranges' WHEN @a=3 THEN 'Bananas' ELSE 'Other fruit' END;

PRINT CASE
        WHEN @a<=1           THEN 'Apples'
        WHEN @a=2            THEN 'Oranges'
        WHEN @a>=3 AND @a<10 THEN 'Bananas'
        ELSE 'Other fruit'
      END;

The result is:

Oranges
Oranges
Oranges

Guide

Reference