SQL 2005+

SQL Server CASE Expressions

by Richard Carr, published at http://www.blackwasp.co.uk/SQLCase.aspx

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.

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