By: Aaron Bertrand | Updated: 2019-04-30 | Comments (5) | Related: 1 | 2 | 3 | 4 | 5 | > TSQL
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:
- SQL Server Data Type Precedence
- Using the CASE expression instead of dynamic SQL in SQL Server
- Dirty Secrets of the CASE Expression
- Data type precedence (Microsoft SQL Docs)
- Data type conversion (Microsoft SQL Docs)
About the author
Aaron Bertrand (@AaronBertrand) is a passionate technologist 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 also blogs at sqlblog.org.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-04-30
