SQL Server CASE Statement

The SQL Server CASE statement evaluates a list of conditions and returns one of multiple possible result expressions.

Although it’s commonly known as the “CASE statement”, the SQL Server documentation lists it as an expression. Plus it’s referred to as the “CASE expression” in the SQL standard. Some other DBMSs distinguish between the CASE statement and the CASE expression. Either way, here’s a quick rundown on how the “CASE statement” (or CASE expression) works in SQL Server.

Syntax

There are two formats of CASE expression:

Simple CASE expression
Searched CASE expression

Here’s the syntax for the simple CASE expression:

CASE input_expression   
     WHEN when_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END

Here’s the syntax for the searched CASE expression:

CASE  
     WHEN Boolean_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END

The return type is the highest precedence type from the set of types in result_expressions and the optional else_result_expression.

Below are examples of each.

The Simple `CASE` Expression

The simple CASE expression compares an expression to a set of simple expressions to determine the result

Example:

DECLARE @stock_ticker varchar(4) = 'V';

SELECT Company =  
    CASE @stock_ticker  
        WHEN 'AAPL' THEN 'Apple'
        WHEN 'FB' THEN 'Meta'
        WHEN 'V' THEN 'Visa'
        ELSE 'Not in the portfolio'  
    END

Result:

+-----------+
| Company   |
|-----------|
| Visa      |
+-----------+

In this example, my CASE expression is part of a SELECT statement. It checks for three conditions and has an ELSE to cater for anything that isn’t covered in the three conditions.

In this case, the stock ticker V matches the third WHEN expression, and the expression provided by THEN is returned.

To be clear, the actual CASE expression is this part:

    CASE @stock_ticker  
        WHEN 'AAPL' THEN 'Apple'
        WHEN 'FB' THEN 'Meta'
        WHEN 'MA' THEN 'Mastercard'
        WHEN 'V' THEN 'Visa'
        ELSE 'Not in the portfolio'  
    END

What CASE does is check the value of each WHEN expression against the input expression. In my example, the @stock_ticker variable is the input expression. Therefore, it’s checking the value of each WHEN expression against the @stock_ticker variable.

When/if it finds a match, it returns the expression provided by THEN.

My example uses three WHEN expressions, but it could have been more and it could have been less, depending on the requirements.

The Searched `CASE` Expression

The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Here’s an example of a searched CASE expression.

DECLARE @price int = 1500;

SELECT Affordability =  
    CASE   
        WHEN @price < 100 THEN 'Cheap'
        WHEN @price >= 100 AND @price < 500 THEN 'Affordable'
        ELSE 'Expensive'  
    END

Result:

+-----------------+
| Affordability   |
|-----------------|
| Expensive       |
+-----------------+

A searched CASE expression doesn’t have an input expression like the simple CASE expression.

You’ll recall that in our simple CASE expression, it started off with CASE @stock_ticker, and therefore we knew that the WHEN expressions were all evaluating against the value of @stock_ticker.

With the searched CASE expression, we don’t provide an input expression at the start like that. Instead, each WHEN expression includes a Boolean expression for which to be evaluated against.

A Database Example

Here’s an example that demonstrates how the CASE expression can be used within a database query.

USE WideWorldImporters;
SELECT 
    CityName AS [City], 
    LatestRecordedPopulation AS [Population], 
    Size =  
      CASE 
         WHEN LatestRecordedPopulation < 2000000 THEN 'Small City'  
         WHEN LatestRecordedPopulation >= 2000000 AND LatestRecordedPopulation < 3000000 THEN 'Big City' 
         ELSE 'Really Big City'
      END 
FROM Application.Cities
WHERE LatestRecordedPopulation > 1000000;

Result:

+--------------+--------------+-----------------+
| City         | Population   | Size            |
|--------------+--------------+-----------------|
| Brooklyn     | 2565635      | Big City        |
| Chicago      | 2695598      | Big City        |
| Dallas       | 1197816      | Small City      |
| Houston      | 2099451      | Big City        |
| Los Angeles  | 3792621      | Really Big City |
| Manhattan    | 1619090      | Small City      |
| New York     | 8175133      | Really Big City |
| Philadelphia | 1526006      | Small City      |
| Phoenix      | 1445632      | Small City      |
| Queens       | 2272771      | Big City        |
| San Antonio  | 1327407      | Small City      |
| San Diego    | 1307402      | Small City      |
| The Bronx    | 1408473      | Small City      |
+--------------+--------------+-----------------+

This example uses a searched CASE expression to evaluate the results from the LatestRecordedPopulation column of the Application.Cities table.

Data Types

In SQL Server, the data type of the input expression and the WHEN expressions must be the same or must be an implicit conversion.

Here’s what happens if they aren’t:

DECLARE @stock_ticker varchar(4) = 'V';

SELECT Company =  
      CASE @stock_ticker  
         WHEN 1 THEN 'Apple'
         WHEN 2 THEN 'Meta'
         WHEN 3 THEN 'Mastercard'
         WHEN 4 THEN 'Visa'
         ELSE 'Not in the portfolio'  
      END

Result:

Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'V' to data type int.

Order of Evaluation

The T-SQL CASE expression evaluates its conditions sequentially and stops with the first condition whose condition is satisfied.

To demonstrate this, let’s use multiple WHEN expressions that share the same value:

DECLARE @stock_ticker varchar(4) = 'V';

SELECT Company =  
    CASE @stock_ticker  
        WHEN 'V' THEN 'Visa 1'
        WHEN 'V' THEN 'Visa 2'
        WHEN 'V' THEN 'Visa 3'
        ELSE 'Not in the portfolio'  
    END

Result:

+-----------+
| Company   |
|-----------|
| Visa 1    |
+-----------+

In this case, it stopped at the first WHEN expression.

There may be the occasional scenario where an expression is evaluated before a CASE expression receives the results of the expression as its input. In such scenarios, you could end up with an error. This could happen if you include an aggregate expression as the WHEN expression.

For this reason, Microsoft advises that:

You should only depend on order of evaluation of the WHEN conditions for scalar expressions (including non-correlated sub-queries that return scalars), not for aggregate expressions.

`ELSE` is Optional

The ELSE argument is optional. Therefore, we could rewrite our “affordability” example as follows:

DECLARE @price int = 1500;

SELECT Affordability =  
    CASE   
        WHEN @price < 100 THEN 'Cheap'
        WHEN @price >= 100 AND @price < 500 THEN 'Affordable'
        WHEN @price >= 500 THEN 'Expensive'
    END

Result:

+-----------------+
| Affordability   |
|-----------------|
| Expensive       |
+-----------------+

Although, be mindful that you could end up with NULL if you omit the ELSE argument.

The following example results in NULL:

DECLARE @price int = 1500;

SELECT Affordability =  
    CASE   
        WHEN @price < 100 THEN 'Cheap'
        WHEN @price >= 100 AND @price < 500 THEN 'Affordable'
        WHEN @price >= 500 AND @price < 1000 THEN 'Expensive'
    END

Result:

+-----------------+
| Affordability   |
|-----------------|
| NULL            |
+-----------------+

Nested `CASE` Expressions

You can nest CASE expressions if required:

DECLARE @price int, @on_sale bit;
SET @price = 1500;
SET @on_sale = 1;

SELECT Affordability =  
    CASE   
        WHEN @price < 100 THEN 'Cheap'
        WHEN @price >= 100 THEN 
            CASE @on_sale
                WHEN 0 THEN 'Expensive (but it''s not currently on sale)' 
                WHEN 1 THEN 'Expensive (and it''s already on sale!)'
            END
    END

Result:

+---------------------------------------+
| Affordability                         |
|---------------------------------------|
| Expensive (and it's already on sale!) |
+---------------------------------------+

However, it’s important to note that only 10 levels of nesting is allowed for CASE expressions in SQL Server. If you try to nest more than 10 levels, then you’ll get an error.

CASE in an ORDER BY Clause

In SQL Server, the CASE expression can be used in any statement or clause that allows a valid expression. Therefore, you can use it in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as IN, WHERE, ORDER BY, GROUP BY, and HAVING.

Using a CASE expression in a statement’s ORDER BY clause can be handy when you want to make a special exception for certain values when ordering your results.

Suppose we run the following query against a table containing music genres.

SELECT Genre 
FROM MusicGenres
ORDER BY Genre ASC;

Result:

+---------+
| Genre   |
|---------|
| Blues   |
| Country |
| Hip Hop |
| Jazz    |
| Metal   |
| Other   |
| Pop     |
| Rap     |
| Rock    |
+---------+

Here, we order the results by the Genre column, in ascending order.

This is fine except for one thing. The genre called Other. Wouldn’t it be nice if we could move Other to the bottom?

We can achieve this with the CASE expression by taking the above query, and modifying it as follows.

SELECT Genre
FROM MusicGenres
ORDER BY 
    CASE Genre
        WHEN 'Other' THEN 1
        ELSE 0
    END
    ASC, Genre ASC;

Result:

+---------+
| Genre   |
|---------|
| Blues   |
| Country |
| Hip Hop |
| Jazz    |
| Metal   |
| Pop     |
| Rap     |
| Rock    |
| Other   |
+---------+

`CASE` in an `UPDATE` Statement

Suppose we have the following table:

+---------+-----------+-----------+----------+
| DogId   | DogName   | GoodDog   | Dinner   |
|---------+-----------+-----------+----------|
| 1       | Fetch     | 1         | NULL     |
| 2       | Fluffy    | 0         | NULL     |
| 3       | Wag       | 0         | NULL     |
| 1001    | Brian     | 1         | NULL     |
| 1002    | Rambo     | 0         | NULL     |
| 1003    | BamBam    | 1         | NULL     |
+---------+-----------+-----------+----------+

And suppose we had recently added the Dinner column, and it’s still NULL, waiting for values to be inserted.

But the values to be inserted will depend on the value of the GoodDog column.

We could use a CASE expression in such a scenario.

UPDATE Dogs 
SET Dinner = 
    CASE GoodDog
        WHEN 1 THEN 'Sunday Roast'
        ELSE 'Airline food'
    END

SELECT * FROM Dogs;

Result:

+---------+-----------+-----------+--------------+
| DogId   | DogName   | GoodDog   | Dinner       |
|---------+-----------+-----------+--------------|
| 1       | Fetch     | 1         | Sunday Roast |
| 2       | Fluffy    | 0         | Airline food |
| 3       | Wag       | 0         | Airline food |
| 1001    | Brian     | 1         | Sunday Roast |
| 1002    | Rambo     | 0         | Airline food |
| 1003    | BamBam    | 1         | Sunday Roast |
+---------+-----------+-----------+--------------+

`CASE` in an `INSERT` Statement

We can take the table from the above example, and insert a new value.

And we can again take advantage of the CASE expression to insert the appropriate value into the Dinner column.

DECLARE @DogName nvarchar(60), @GoodDog bit;
SET @DogName = 'Lazy';
SET @GoodDog = 0;

INSERT INTO Dogs ( DogName, GoodDog, Dinner )
VALUES (
    @DogName,
    @GoodDog,
    CASE @GoodDog
        WHEN 1 THEN 'Sunday Roast'
        ELSE 'Airline food'
    END
    );

SELECT * FROM Dogs;

Result:

+---------+-----------+-----------+--------------+
| DogId   | DogName   | GoodDog   | Dinner       |
|---------+-----------+-----------+--------------|
| 1       | Fetch     | 1         | Sunday Roast |
| 2       | Fluffy    | 0         | Airline food |
| 3       | Wag       | 0         | Airline food |
| 1001    | Brian     | 1         | Sunday Roast |
| 1002    | Rambo     | 0         | Airline food |
| 1003    | BamBam    | 1         | Sunday Roast |
| 1004    | Lazy      | 0         | Airline food |
+---------+-----------+-----------+--------------+

This time the CASE expression was evaluating the value of a variable we’d just set, then inserting the appropriate value into the Dinner column.

Having one column being dependent on the value of another column is a good scenario for setting up a computed column.

Syntax

The Simple CASE Expression

The Searched CASE Expression

A Database Example

Data Types

Order of Evaluation

ELSE is Optional

Nested CASE Expressions

CASE in an ORDER BY Clause

CASE in an UPDATE Statement

CASE in an INSERT Statement

The Simple `CASE` Expression

The Searched `CASE` Expression

`ELSE` is Optional

Nested `CASE` Expressions

`CASE` in an `UPDATE` Statement

`CASE` in an `INSERT` Statement