Chapter 2: Transact-SQL Functions

case

Description

Supports conditional SQL expressions; can be used anywhere a value expression can be used.

Syntax

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

Parameters

case: begins the case expression.

when: precedes the search condition or the expression to be compared.

search_condition: 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.

then: precedes the expression that specifies a result value of case.

expression: 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” in.

Examples

Example 1

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

Example 2

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

Yuo can also use the following format to produce the same result, since coalesce is an abbreviated form of a case expression:

select stor_id, discount, 
        case
            when lowqty is not NULL then lowqty
            else highqty
        end
from discounts

Example 3

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

You can also use the following format to produce the same result, since nullif is an abbreviated form of a case expression:

select title, 
        case
            when type = "UNDECIDED" then NULL
            else type
        end
from titles

Example 4

Produces an error message, because at least one expression must be something other than the null keyword:

select price, coalesce (NULL, NULL, NULL)
from titles

All result expressions in a CASE expression must not be NULL.

Example 5

Produces an error message, because at least two expressions must follow coalesce:

select stor_id, discount, coalesce (highqty)
from discounts

A single coalesce element is illegal in a COALESCE expression.

Usage

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.
If your query produces a variety of datatypes, the datatype of a case expression result is determined by datatype hierarchy, as described in “Datatypes of mixed-mode expressions” in. If you specify two datatypes that Adaptive Server cannot implicitly convert (for example, char and int), the query fails.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

case permission defaults to all users. No permission is required to use it.