You probably don’t know as much about how the CASE Expression works in Transact SQL as you think. In this article by Aaron Bertrand, we find that even Microsoft has a difficult time getting the documentation on this important expression correct.
The official documentation implies that the entire expression will short-circuit, meaning it will evaluate the expression from left-to-right, and stop evaluating when it hits a match:
The CASE statement [sic!] evaluates its conditions sequentially and stops with the first condition whose condition is satisfied.
However, this isn’t always true. And to its credit, at least in the 2014 docs, the page goes on to try to explain one scenario where this isn’t guaranteed. But it only gets part of the story:
In some situations, an expression is evaluated before a CASE statement [sic!] receives the results of the expression as its input. Errors in evaluating these expressions are possible. Aggregate expressions that appear in WHEN arguments to a CASE statement [sic!] are evaluated first, then provided to the CASE statement [sic!]. For example, the following query produces a divide by zero error when producing the value of the MAX aggregate. This occurs prior to evaluating the CASE expression.
The divide by zero example is pretty easy to reproduce, and I demonstrated it in
DECLARE @i INT = 1; SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1/0) END;
Msg 8134, Level 16, State 1 Divide by zero error encountered.
There are trivial workarounds (such as
ELSE (SELECT MIN(1/0)) END), but this comes as a real surprise to many who haven’t memorized the above sentences from Books Online.