SQL 2005+

SQL Server CASE Expressions

The fifty-third part of the SQL Server Programming Fundamentals tutorial examines one of the conditional statements available to the Transact-SQL (T-SQL) developer. CASE expressions allow a value to be determined based upon a number of conditions.

CASE Expressions

The operators and functions that have been described in the earlier articles of this tutorial can be used within queries, data manipulation statements and stored procedures to allow complex interaction with the data in a database. However, few of the techniques described so far have allowed the introduction of conditional operations to allow completely different results based upon one or more tests.

In this article we will examine the CASE statement. This powerful statement allows you to evaluate expressions and return different values according to the results. CASE expressions can be used wherever any standard expression is permitted. For example, a CASE statement may appear as a column in a query or update statement, within WHERE, HAVING and ORDER BY clauses, in the assignment of variables and in many other places. The CASE statement is not a control-of-flow command that might be used in a multi-statement script or stored procedure. For such a command see the next instalment in the tutorial, which describes the IF statement.

Unusually, the CASE statement has two different syntaxes. The simple expression variation includes a key value and a list of values that it should be compared with. If one of the list items matches the key value, a corresponding result is returned. A common use of the simple CASE statement is the expansion of short codes in the database. If the key value is returned from a single character column that may contain "M", "F" or "U", the CASE statement can be used to expand these to "Male", "Female" or "Unknown".

The second syntax is known as the searched expression. In this style of statement a series of Boolean predicates, which may be entirely unrelated, are tested. Each predicate is linked to a return value. The value linked to the first of the predicates that evaluates as true is returned. As Boolean expressions are used instead of discrete values, searched CASE expressions are often used to determine the position of a value within a set of ranges.

Using CASE Expressions

The examples in this article are queries that may be executed against the JoBS database. This is the database that has been created throughout the course of the tutorial. If you do not have an up to date copy of the database, please download and execute the creation script using the link at the top of this page.

Simple CASE Expressions

Simple CASE expressions use a key input value or expression that is compared to a list of options. Where the key value and one of the options match, a corresponding value is returned. The input value is preceded by the CASE clause, the key values by the WHEN clause and the corresponding results by the THEN clause. The statement is terminated with the END keyword. The basic syntax for the statement is as follows:

CASE input-value
    WHEN compare-expression-1 THEN result-expression-1
    WHEN compare-expression-2 THEN result-expression-2
    .
    .
    .
    WHEN compare-expression-X THEN result-expression-X
END

We can demonstrate the simple expression syntax with an example query. In the sample below, all of the rows are retrieved from the Customer table of the JoBS database. The first column in the result set contains the name of the customer. The second column shows "Early Adopter" for all of the customers who joined the service in the year 2007 and "Late Starter" for those who waited until 2008. If there were any customers with a different creation date, the column would contain NULL for those rows.

SELECT
    FirstName + ' ' + LastName AS FullName,
    CASE year(CreatedDate)
        WHEN 2007 THEN 'Early Adopter'
        WHEN 2008 THEN 'Late Starter'
    END AS CustomerType
FROM
    Customers

The ELSE Clause

The CASE statement can include an ELSE clause before the END keyword. This clause is used to specify a default value or expression to return when the input value matches none of the provided options. The following query demonstrates this by returning all of the contracts and the number of jobs that have been undertaken for each. The final column calculates the price per job for each contract by dividing the contract value by the job count. The CASE statement prevents division by zero errors by intercepting rows with zero jobs and returning the contract value. For all other cases, the ELSE clause performs the appropriate calculation.

SELECT 
    C.ContractNumber,
    C.ContractValue,
    count(J.JobId) AS Jobs,
    CASE count(J.JobId)
        WHEN 0 THEN ContractValue
        ELSE C.ContractValue / count(J.JobId)
    END AS PricePerJob
FROM
    Contracts C
LEFT JOIN
    Jobs J
ON
    C.ContractNumber = J.ContractNumber
GROUP BY
    C.ContractNumber,
    C.ContractValue

Searched CASE Expressions

Searched CASE expressions use a different syntax to the simple variation. The CASE keyword is used to indicate the start of the expression but does not include a key value. Each WHEN clause is followed by a Boolean expression, or predicate, a THEN clause and a resultant value. The WHEN clauses are evaluated from left to right until the first predicate that is true is found. The corresponding result is then returned. If no predicates are true, the value from the ELSE clause, or NULL if no ELSE clause is present, is returned.

CASE
    WHEN predicate-1 THEN result-expression-1
    WHEN predicate-2 THEN result-expression-2
    .
    .
    .
    WHEN predicate-X THEN result-expression-X
    ELSE else-expression
END

In the following query a searched CASE expression is used to provide advice on engineer selection to the user. The query returns a list of every engineer in the database with his or her ID, name and standard hourly rate. The fourth column uses a CASE expression to determine which of three ranges the hourly rate falls within and show an appropriate message. If the rate is not within any of the three ranges, the ELSE clause provides a default message.

SELECT
    EngineerId,
    EngineerName,
    HourlyRate,
    CASE
        WHEN HourlyRate < 18 THEN 'Preferred'
        WHEN HourlyRate BETWEEN 18 AND 19.99 THEN 'Standard'
        WHEN HourlyRate BETWEEN 20 AND 23.99 THEN 'High Cost'
        ELSE 'Emergency Use Only'
    END AS 'EngineerSelection'
FROM
    Engineers
ORDER BY
    HourlyRate

Nesting CASE Statements

CASE statements can be nested to provide complex conditional logic. They are limited to nesting of ten levels deep. This is generally not a problem as nesting to such a level would generally be too difficult to read and maintain. The final sample query uses nesting with two levels. The outer CASE expression determines whether each customer is a business or personal customer. For personal customers, the inner CASE expression selects one of three filing locations based upon the first letter of their surname. For business customers the second nested CASE expression selects a location based upon the first character of the business name.

SELECT
    CustomerNumber,
    FirstName + ' ' + LastName AS FullName,
    BusinessName,
    CASE
        WHEN BusinessName IS NULL THEN
            CASE
                WHEN left(LastName, 1) BETWEEN 'A' AND 'J' THEN 'Personal A-J'
                WHEN left(LastName, 1) BETWEEN 'K' AND 'S' THEN 'Personal K-S'
                ELSE 'Personal T-Z'
            END
        ELSE
            CASE
                WHEN left(BusinessName, 1) BETWEEN 'A' AND 'M' THEN 'Business A-M'
                WHEN left(BusinessName, 1) BETWEEN 'N' AND 'Z' THEN 'Business N-Z'
                ELSE 'Business Other'
            END
    END AS FilingArea
FROM
    Customers
ORDER BY
    BusinessName,
    LastName