SQL 2005+

SQL Server CASE Expressions (2)

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.

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