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.

Syntax

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

Arguments

  • 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.

Result Types

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

Example

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