Understanding the CASE Expression in SQL Server

sqlserver2014

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;

Result:

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. 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s