Export (0) Print
Expand All
Expand Minimize

CASE (SQL Server Compact)

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.


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

CASE
    WHEN Boolean_expression THEN result_expression 
        [ ...n ] 
    [ 
        ELSE else_result_expression 
    ] 
END

input_expression

The expression evaluated when you use the simple CASE format. The input_expression argument is any valid expression in Microsoft SQL Server Compact 3.5 (SQL Server Compact 3.5).

WHEN when_expression

A simple expression to which input_expression is compared when you use the simple CASE format. The when_expression argument 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

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

The expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE. The result expression argument is any valid SQL Server expression.

ELSE else_result_expression

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. The else_result_expression argument 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

The Boolean expression evaluated when you use the searched CASE format. Boolean_expression is any valid Boolean expression.

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

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.

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

SELECT [Ship Via], CASE [Ship Via]
    WHEN 1 THEN 'A.Datum'
    WHEN 2 THEN 'Contoso'
    WHEN 3 THEN 'Consolidated Messenger'
        ELSE 'Unknown'
        END
FROM Orders
Show:
© 2014 Microsoft