Transact-SQL Desk Reference

B.3. CASE AND COALESCE

The CASE and COALESCE expressions give capabilities for decision-making similar to multi-way branching statements in some 3GLs. They each return a single value based on the input(s).

B.3.1. CASE—ANSI and MSS

There are two forms of syntax for the CASE expression.

Syntax: Form 1—Simple CASE, exact match

CASE input-expression  WHEN match-expression THEN result
                   [ WHEN match-expression THEN result ]
                   ...
                   [ELSE result]

Syntax: Form 2—Searched CASE, inequality and general Boolean condition

CASE           WHEN Boolean-condition THEN result
                  [ WHEN Boolean-condition THEN result ]
                  ...
                  [ELSE result2]

Note: An expression written using the first form could be rewritten using the second form by explicitly stating the WHEN condition for each value: ...