Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server CASE Expression Overview


By:   |   Updated: 2019-04-30   |   Comments (5)   |   Related: More > T-SQL

Problem

CASE is one of the most powerful and more complex built-in expressions in Transact-SQL. Due to its name, this expression is regularly mistaken for the CASE statement available in some other languages. In SQL Server, the purpose of the CASE expression is to always return an expression. It’s not intended for control of flow, which is why we don’t call it a CASE statement. In this tip, I share some of the finer points on how to best use a CASE expression.

Solution

There are two distinct options for the CASE expression that aren’t apparent from any syntax diagram: the "simple" CASE expression, and the "searched" CASE expression. The simple variation starts with an expression and compares that (equality only) to each of the possible evaluations (these can be expressions, but are typically constants or variables):

CASE [input_expression]
  WHEN [eval_expression] THEN [output_expression]
  …
  ELSE [output_expression]
END

The "searched" variation does not start with an input expression, but rather dives straight into WHEN evaluations (these are full expressions that can be evaluated, and aren’t restricted to equality):

CASE 
  WHEN [full_eval_expression] THEN [output_expression]
  …
  ELSE [output_expression]
END

Examples:

DECLARE @variable int = 5;

SELECT [simple] = CASE @variable
  WHEN 1 THEN 'One'
  WHEN 5 THEN 'Five'
  ELSE 'Some other number'
END;

SELECT [searched] = CASE
  WHEN @variable = 1 THEN 'One'
  WHEN @variable = 5 THEN 'Five'
  ELSE 'Some other number'
END;

I find the searched variation is used more often, since it so much more flexible. For example, if I want to check the values of two variables, or use any kind of comparison other than equality, I can do this:

SELECT CASE WHEN @a > 5 OR @b <= 10 THEN c + @a - @b ELSE c - @b + @a END FROM dbo.table;

In either case, the result is always a single expression.

I often see people trying to do something like below. CASE simply cannot change the shape of the output.

SELECT 
  CASE @Detailed
    WHEN 0 THEN Id, Name
    WHEN 1 THEN Id, Name, Description, Email, …
  END
FROM …

Below is another thing people try to do. CASE also cannot change the entities involved in a query.

SELECT * FROM 
  CASE @Detailed
    WHEN 0 THEN dbo.Orders
    WHEN 1 THEN dbo.OrderDetails
  END;

Short Circuiting

Whether using simple or searched, you can have many WHEN clauses, just note that only one expression can evaluate to true. In most cases, you can rely on the WHEN expressions to be evaluated sequentially, as written, and return the first evaluation that evaluates to true.

For example, it is safe to assume that in this example, putting a doomed expression in the ELSE clause will never make the statement fail:

DECLARE @i int = 0;

SELECT CASE
  WHEN @a =  0 THEN  0
  WHEN @a >= 0 THEN +1
  WHEN @a <  0 THEN -1
  ELSE 1/0
END;

Two things to note here. The first is that in the case where @a = 0, this expression will always return 0, even though that specific value also satisfies the second evaluation (which would return 1). The second is that the ELSE condition will never be reached in this scenario, because there is no possible value for @a that won’t be captured by a previous WHEN evaluation.

As with most things, though, there are exceptions.

The first is that, when aggregates are involved, you can no longer rely on sequential evaluation. Change the ELSE expression to this:

ELSE MIN(1/0)

This forces the aggregation to happen before the evaluation, and will generate a runtime error immediately (though it will parse and compile just fine):

Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.

The second exception is a common assumption that an ELSE can never be reached, and there are definitely scenarios where you can fall out of the evaluation branches even when you think they should all be covered. I’ll illustrate with another example. Let’s say we want to use a CASE expression to flip a coin:

SELECT CASE CONVERT(int, RAND() + 1.5)
  WHEN 1 THEN 'Heads'
  WHEN 2 THEN 'Tails'
END;

There should only be two possible outcomes to this expression, 1 or 2, but in some cases this yields NULL. We can add that as an ELSE condition, but it doesn’t add any insight:

SELECT CASE CONVERT(int, RAND() + 1.5)
  WHEN 1 THEN 'Heads'
  WHEN 2 THEN 'Tails'
  ELSE 'NULL? Why?'
END;

The reason is that the above is actually implemented internally as a searched expression:

SELECT CASE 
  WHEN CONVERT(int, RAND() + 1.5) = 1 THEN 'Heads'
  WHEN CONVERT(int, RAND() + 1.5) = 2 THEN 'Tails'
  ELSE 'NULL? Why?'
END;

What can happen here is that the first WHEN evaluates, and let’s say it yields a 2, the expression moves on to the next WHEN, and this time RAND() is evaluated a second time, and this time it yields a 1. Now the expression has no choice but to return the ELSE condition. The workaround for this is to assign the output of any non-deterministic expressions to a variable first, to ensure they are evaluated exactly once:

DECLARE @i int = CONVERT(int, RAND() + 1.5);

SELECT CASE @i
  WHEN 1 THEN 'Heads'
  WHEN 2 THEN 'Tails'
  ELSE 'NULL? Why?'
END;

Now the ELSE will never be reached.

Data Type Precedence

The data type returned by a CASE expression is determined by standard data type precedence rules. Precedence is evaluated across all possible results, again in order (though all bets are still off when aggregates come into play). The following statement will succeed, because the evaluation never hits an expression that can’t be converted to the data type inferred from the first possible outcome (integer):

DECLARE @i int = 1;
SELECT CASE @i
  WHEN 1 THEN 3
  ELSE 'foo'
END;

However, if you change @i to 2, you will get an error because evaluation moves past the first possible outcome, but still considers that to be the data type needed for output, and 'foo' cannot be converted to an integer:

DECLARE @i int = 2;
SELECT CASE @i
  WHEN 1 THEN 3
  ELSE 'foo'
END;

Error message:

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

Data type precedence is a complex topic – I recommend bookmarking this documentation topic, this tip by Armando Prato, and this conversion chart. My suggestion will always be to implicitly or explicitly convert all possible output expressions to the lowest common denominator. In the above example that would mean expressing as a string any expression that is not obviously a string already:

DECLARE @i int = 2;
SELECT CASE @i
  WHEN 1 THEN '3' -- or use CONVERT()
  ELSE 'foo'
END;

CASE as the Unsung Hero

Many of SQL Server’s other built-in functions and expressions use CASE under the covers. A couple of examples, in addition to my illustration of TRY_CONVERT() in this previous tip:

COALESCE(a,b)
-- or
ISNULL(a,b) -- …are actually… CASE WHEN a IS NOT NULL THEN a ELSE b END
NULLIF(a,b) -- …is actually… CASE WHEN a = b THEN NULL ELSE a END
IIF(a > b, c, d) -- …is actually… CASE WHEN a > b THEN c ELSE d END

It is important to note that in the COALESCE() example specifically, this can lead to a being evaluated once to check that it is NOT NULL and then evaluated again to return the value. This is unimportant in many scenarios, until you have something like this:

SELECT COALESCE((SELECT COUNT(*) FROM [big table]), 0);

Which actually runs the COUNT() twice:

SELECT CASE WHEN (SELECT COUNT(*) FROM [big table]) IS NOT NULL
    THEN (SELECT COUNT(*) FROM [big table])
    ELSE 0
END;

This evaluation is hidden away in the shorthand syntax offered by COALESCE(), but it is apparent in the execution plan. An interesting side note here is that ISNULL() follows a slightly different code path and seems smart enough to only evaluate the expression a single time.

(Other than this pitfall, there aren’t really any performance implications with using or not using a CASE expression in any query, or using alternatives like the built-in shorthand functions mentioned above.)

Where You Can Use CASE

CASE can be used just about anywhere a column, variable, expression, or constant can be used. This is all valid:

DECLARE @i int = 1;
SELECT name,
  FirstLetter = MAX(CASE
      WHEN name LIKE N'S%' THEN 'Starts with S'
      ELSE 'Does not start with S'
    END)
  FROM sys.databases
WHERE CASE @i
    WHEN 1 THEN name
    ELSE recovery_model_desc END
  = CASE @i
    WHEN 2 THEN recovery_model_desc
    WHEN 5 THEN name
    ELSE 'Some constant'
  END
GROUP BY CASE @i WHEN 2 THEN name ELSE REVERSE(name) END
ORDER BY CASE WHEN database_id < 5 THEN database_id END;

That’s a query that’s both ugly and useless; just an extreme example of using CASE expressions all over the place.

Considerations for Nesting

CASE expressions can be nested:

DECLARE @tier int = 2;
SELECT
CASE WHEN @tier > 1 THEN
   CASE WHEN @tier > 2 THEN
     CASE WHEN @tier > 3 THEN
       CASE WHEN @tier > 4 THEN
         CASE WHEN @tier > 5 THEN
           'Awesome'
         ELSE 'Great' END
       ELSE 'Good' END
     ELSE 'Acceptable' END
   ELSE 'Poor' END
ELSE 'Invalid' END;

But be careful; this can become complex very quickly, especially when mixing data types, and there is a hard limit on number of nested CASE expressions: 10. So this is invalid:

DECLARE @i int = 1;
SELECT 
  CASE @i WHEN 1  THEN CASE @i WHEN 2  THEN
  CASE @i WHEN 3  THEN CASE @i WHEN 4  THEN
  CASE @i WHEN 5  THEN CASE @i WHEN 6  THEN
  CASE @i WHEN 7  THEN CASE @i WHEN 8  THEN
  CASE @i WHEN 9  THEN CASE @i WHEN 10 THEN
  CASE @i WHEN 11 THEN 'Whoopsies'
END END END END END END END END END END END;

Error message:

Msg 125, Level 15, State 3, Line 9
Case expressions may only be nested to level 10.

Note that when running a CASE expression against a linked server, the optimizer on the other side may expand this to a nested CASE expression and cause this issue, even though it’s not what you wrote; see this example from Paul White.

Summary

The CASE expression is powerful, but has some nuances that often surprise new users. If you’re using CASE expressions, you should become familiar with how they work and, more importantly, when they might yield unexpected results.

Next Steps

Read on for related tips and other resources:



Last Updated: 2019-04-30


get scripts

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, April 30, 2019 - 2:01:19 PM - Joe Celko Back To Top

I think this was one of the best articles on the CASE expression I've ever seen. The only thing I would change is a little more explanation about "levels of aggregation" to explain how having a WHEN clause that involves an aggregation determines that all of the other clauses must have the same level of aggregation.

As a bit of historical note, let me add that the CASE expression was introduced in the ANSI X3H2 committee from the ADA programming language. Back in those days, this was the new super language it was going to take over everything. Other languages have similar constructs, but we took the syntax from ADA. I cannot remember who introduced it, but I would guess it was Jim Melton because he was involved with a lot of the "computational" aspects of the SQL standards when he was on the committee.


Tuesday, April 30, 2019 - 11:21:02 AM - Aaron Bertrand Back To Top

@Rob yes, sorry, I meant "... no possible NON-NULL value ..."


Tuesday, April 30, 2019 - 10:30:45 AM - Rob Volk Back To Top

Hi Aaron, 

One comment on the short circuiting segment, you state that "...because there is no possible value for @a that won’t be captured by a previous WHEN evaluation". While true for the scenario you set up, setting @a to NULL would trigger the ELSE condition. It might help to clarify that point, even though you discuss NULL further down.

-Rob


Tuesday, April 30, 2019 - 7:53:15 AM - Aaron Bertrand Back To Top

Thanks @Giles, you're right, I definitely went overboard on simplifying the examples. Hopefully the intent is clear without having to copy / paste / execute. :-)


Tuesday, April 30, 2019 - 3:58:02 AM - Giles Sutcliffe Back To Top

Excellent article, thank you. Some important things I hadn't realised about aggregates in CASE expressions.

One tiny typo: In the code following "For example, it is safe to assume that in this example, putting a doomed expression in the ELSE clause will never make the statement fail:", you are declaring @i, then referencing @a in the CASE.


Learn more about SQL Server tools