CASE (SQL Server Compact Edition)

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 2005 Compact Edition (SQL Server Compact Edition).
  • 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.

Return Value

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

Example

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

SELECT ShipVia, CASE ShipVia 
    WHEN 1 THEN 'A.Datum'
    WHEN 2 THEN 'Contoso'
    WHEN 3 THEN 'Consolidated Messenger'
        ELSE 'Unknown'
        END
FROM Orders