case Expression

case expression simplifies many conditional Transact-SQL constructs. Instead of using a series of if statements, case expression allows you to use a series of conditions that return the appropriate values when the conditions are met. case expression is ANSI-SQL-compliant.

case expression includes the keywords case, when, then, coalesce, and nullif. coalesce and nullif are an abbreviated form of case expression. See the Reference Manual: Commands.

• case Expression for Alternative Representation
  Use case expression to represent data in a manner that is more meaningful, for example, to display a phrase rather than a binary (1, 0) setting.
• case and Division by Zero
  case expression allows you to write queries that avoid division by zero (called exception avoidance).
• rand Functions in case Expressions
  Expressions that reference the rand function, the getdate function, and so on, produce different values each time they are evaluated. This can yield unexpected results when you use these expressions in certain case expressions.
• case Expression Results
  case expression uses a series of alternative result expressions.
• case Expressions and set ansinull
  Case expressions may produce different results for null values if you enable set ansinull and include a when NULL clause in certain queries.
• case Expression Requires at Least one Non-Null Result
  At least one result from the case expression must return a value other than null.
• Determining the Result Set
  Use case expression to test for conditions that determine the result set.
• case and Value Comparisons
  case can be used for value comparisons. It allows only an equality check between two values; no other comparisons are allowed.
• coalesce
  coalesce examines a series of values (value1, value2, ..., valuen) and returns the first non-null value.
• nullif
  Use nullif to find any missing, unknown, or inapplicable information that is stored in an encoded form.