BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
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
16 December 2009