CASE (Entity SQL)

  • Article

Evaluates a set of Boolean expressions to determine the result.

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

Arguments

  • n
    Is a placeholder that indicates that multiple WHEN when_expression THEN result_expression clauses, or multiple WHEN Boolean_expression THEN result_expression clauses can be used.
  • THEN result_expression
    Is the expression returned when Boolean_expression evaluates to true. result expression is any valid expression.
  • ELSE else_result_expression
    Is 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. else_result_expression is any valid 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
    Is the Boolean expression evaluated when the searched CASE format is used. Boolean_expression is any valid Boolean expression.

Return Value

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

Remarks

The Entity SQL case expression resembles the Transact-SQL case expression. You use the case expression to make a series of conditional tests to determine which expression will yield the appropriate result. This form of the case expression applies to a series of one or more Boolean expressions to determine the correct resulting expression.

The CASE function evaluates Boolean_expression for each WHEN clause in the order specified, and returns result_expression of the first Boolean_expression that evaluates to true. If no Boolean_expression evaluates to true, the Database Engine returns the else_result_expression if an ELSE clause is specified, or a null value if no ELSE clause is specified.

A CASE statement cannot return a multiset.

Example

The following Entity SQL query uses the CASE expression to evaluate a set of Boolean expressions in order to determine the result. The query is based on the AdventureWorks Sales Model. To compile and run this query, follow these steps:

  1. Follow the procedure in How to: Execute a Query that Returns PrimitiveType Results (EntityClient).

  2. Pass the following query as an argument to the ExecutePrimitiveTypeQuery method:

CASE WHEN AVG({25,12,11}) < 100 THEN TRUE ELSE FALSE END

The output is shown below:

Value: True

See Also

Reference

ELSE (Entity SQL)
WHEN (Entity SQL)
THEN (Entity SQL)
SELECT (Entity SQL)

Concepts

Entity SQL Reference