Export (0) Print
Expand All

CASE

SQL Server 2000

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

CASE has two formats:

  • The simple CASE function compares an expression to a set of simple expressions to determine the result.
  • The searched CASE function evaluates a set of Boolean expressions to determine the result.

Both formats support an optional ELSE argument.

Syntax

Simple CASE function:

CASE input_expression 
    WHEN when_expression THEN result_expression 
        [ ...n ] 
    [ 
        ELSE else_result_expression 
    ] 
END 

Searched CASE function:

CASE
    WHEN Boolean_expression THEN result_expression 
        [ ...n ] 
    [ 
        ELSE else_result_expression 
    ] 
END
Arguments
input_expression
Is the expression evaluated when using the simple CASE format. input_expression is any valid expression in Microsoft® SQL Server™ 2000 Windows® CE Edition (SQL Server CE).
WHEN when_expression
Is a simple expression to which input_expression is compared when using the simple CASE format. when_expression is any valid SQL Server expression. The data types of input_expression and each when_expression must be the same or must be implicitly converted.
n
Is a placeholder indicating that multiple WHEN when_expression THEN result_expression clauses, or multiple WHEN Boolean_expression THEN result_expression clauses can be used.
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 SQL Server 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 SQL Server 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.
Result Types

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

Result Values

Simple CASE function:

  • 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, else_result_expression is returned if an ELSE clause is specified, or NULL if no ELSE clause is specified.

Searched CASE function:

  • 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, else_result_expression is returned if an ELSE clause is specified, or NULL if no ELSE clause is specified.
Examples

The following example returns the mode of shipping used for orders placed in the Northwind database.

SELECT ShipVia, CASE ShipVia 
    WHEN 1 THEN 'Speedy Express'
    WHEN 2 THEN 'United Package'
    WHEN 3 THEN 'Federal Shipping'
        ELSE 'Unknown'
        END
FROM Orders

Show:
© 2014 Microsoft