SQL Server CASE Expression

Posted on by Ian

In SQL Server, the T-SQL CASE expression is a scalar expression that returns a value based on conditional logic. It evaluates a list of conditions and returns a value, based on the outcome of those conditions..

In some ways, the SQL Server CASE expression is similar to IF...ELSE. However, CASE allows you to check for multiple conditions, whereas IF...ELSE doesn’t.

Also, in SQL Server, IF...ELSE is a control-of-flow language keyword, whereas CASE is not. The CASE expression cannot be used to control the flow of execution of T-SQL statements, statement blocks, user-defined functions, and stored procedures.

The 2 Forms of CASE Expression

There are two forms of CASE expression in SQL Server:

  • Simple CASE expression
  • Searched CASE expression

These are explained with examples below.

Form 1 – The Simple CASE Expression

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

Here’s a basic example to demonstrate how a CASE expression works in SQL Server.

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

SELECT Company =  
    CASE @stock_ticker  
        WHEN 'AAPL' THEN 'Apple'
        WHEN 'FB' THEN 'Facebook'
        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 'Facebook'
        WHEN 'MA' THEN 'Mastercard'
        WHEN 'V' THEN 'Visa'
        ELSE 'Not in the portfolio'  
    END

What CASE does is, it checks 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, then 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 my requirements.

Form 2 – 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 'Facebook'
         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            |
+-----------------+

In such cases, we could always add an ELSE argument, just in case (sorry about the pun!):

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'
        ELSE 'Unknown'
    END

Result:

+-----------------+
| Affordability   |
|-----------------|
| Unknown         |
+-----------------+

Admittedly, this example is probably a bit contrived. After all, there’s no need to cap “expensive”. If something is expensive at under $1000, then it’s also expensive if it’s over $1000.

But the point is, you can use ELSE to catch anything that isn’t covered by the WHEN expression/s.

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

As mentioned, the T-SQL 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

Here’s an example of using a CASE expression 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     |
+---------+-----------+-----------+----------+

We have 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.

Is it a CASE Statement or CASE Expression?

In SQL, lots of things are referred to as a “statement” when in fact, they’re something else. This also appears to be true for the T-SQL “CASE statement”.

Although it’s often referred to as the CASE statement, it’s more accurate to call it the CASE expression. This is also how the Microsoft documentation refers to it.

In SQL Server, rather than being a statement itself, CASE can be used in any statement or clause that allows a valid expression. An expression is a combination of symbols and operators that are evaluated to obtain a single data value.

However, some DBMSs distinguish between the CASE statement, and the CASE expression, and have a slightly different syntax for each. MySQL distinguishes between the CASE statement and the CASE operator, which is essentially the same as the CASE expression.