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.

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.

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
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft