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