Supports conditional SQL expressions; can be used anywhere a value expression can be used.
case when search_condition then expression [when search_condition then expression]... [else expression] end
case and values syntax:
case expression when expression then expression [when expression then expression]... [else expression] end
begins the case expression.
precedes the search condition or the expression to be compared.
is used to set conditions for the results that are selected. Search conditions for case expressions are similar to the search conditions in a where clause. Search conditions are detailed in the Transact-SQL User’s Guide.
precedes the expression that specifies a result value of case.
is a column name, a constant, a function, a subquery, or any combination of column names, constants, and functions connected by arithmetic or bitwise operators. For more information about expressions, see “Expressions”.
Selects all the authors from the authors table and, for certain authors, specifies the city in which they live:
select au_lname, postalcode, case when postalcode = "94705" then "Berkeley Author" when postalcode = "94609" then "Oakland Author" when postalcode = "94612" then "Oakland Author" when postalcode = "97330" then "Corvallis Author" end from authors
Returns the first occurrence of a non-NULL value in either the lowqty or highqty column of the discounts table:
select stor_id, discount, coalesce (lowqty, highqty) from discounts
This is an alternative way of writing Example 2:
select stor_id, discount, case when lowqty is not NULL then lowqty else highqty end from discounts
Selects the titles and type from the titles table. If the book type is UNDECIDED, nullif returns a NULL value:
select title, nullif(type, "UNDECIDED") from titles
This is an alternative way of writing Example 4:
select title, case when type = "UNDECIDED" then NULL else type end from titles
case expression simplifies standard SQL expressions by allowing you to express a search condition using a when...then construct instead of an if statement.
case expressions can be used anywhere an expression can be used in SQL.
At least one expression must be something other than the null keyword. This example produces the following error message:
select price, coalesce (NULL, NULL, NULL) from titles
All result expressions in a CASE expression must not be NULL.
If your query produces a variety of datatypes, the datatype of a case expression result is determined by datatype hierarchy, as described in Datatype of mixed-mode expressions in Chapter 1, “System and User-Defined Datatypes,” in Volume 1, Building Blocks. If you specify two datatypes that Adaptive Server cannot implicitly convert (for example, char and int), the query fails.
coalesce must be followed by at least two expressions. This example produces the following error message:
select stor_id, discount, coalesce (highqty) from discounts
A single coalesce element is illegal in a COALESCE expression.
SQL92 – Compliance level: Transact-SQL extension.
case permission defaults to all users. No permission is required to use it.
Commands coalesce, nullif, if...else, select, where clause