Quick Case Statement Quiz
Published: 31st October 2014
Everyone uses the case statement… I see it all over the place and, to be honest, nearly everyone uses it completely correctly.

However, there are those instances in which I see it used incredibly badly so I thought I’d make it into a quick quiz. (Although it’s not really long enough to be a quiz, and it contains answers… therefore more just me showing examples really).

So let’s dive in to some simple uses of the Case Statement and see if we can predict the output…

When will this statement cease evaluating and return our result?

declare @int int = 1

select case
      
when @int = 1 then 'Bob'
      
when @int = 2 then 'Jeff'
      
end as result


If you said “It will stop processing when it resolves, therefore after the first WHEN” then you are correct.

This is exactly what we expect the Case Statement to do and why we use it.

So… knowing this, what will happen with this case statement?

declare @int int = 1

select case
      
when @int = 1 then 100
      
when @int = 2 then 1/0
      
end as result


Correct… it will evaluate at 1, return the answer, and then stop. All is well.

So what about this?

declare @int int = 1

select case
      
when @int = 1 then 100
      
when @int = 2 then max(1/0)
      
end as result


If you said this will work, then you’re sadly incorrect… this will fail.

The reason for this is because SQL Server seems to process aggregates before it performs the evaluation. Therefore it performs the aggregate “max(1/0)” which fails, and therefore the statement is never even executed.

This really isn’t a common occurrence to be honest, just something to be aware of in case you ever come across it.

Next, what about this?

declare @int int = 1

select case @int
      
when 1 then 100
      
when 2 then 999
      
end as result


Correct… this will return 100 as we expect.

So why did I mention this? Well, it’s more to point out that internally SQL Server actually re-writes our statement as follows:

declare @int int = 1

select case
      
when @int = 1 then 100
      
when @int = 2 then 999
      
else null
      
end as result


Again, why is this important?

Well, consider the following:

select case convert(tinyint, 1+rand()*3)
      
when 1 then 1
      
when 2 then 2
      
when 3 then 3 end


This doesn’t look bad so go ahead and run it a few times. What you should find is that you could end up seeing the following:

We know that our rand statement can only ever return 1, 2, or 3… so how can we get NULL as an answer?

This is why I made mention above as to how SQL Server re-writes this internally…

select case when convert(tinyint, 1+rand()*3) = 1 then 1
          
when convert(tinyint, 1+rand()*3) = 2 then 2
          
when convert(tinyint, 1+rand()*3) = 3 then 3
          
else null end


Now it should become obvious. SQL Server calculates the rand value at every step. Therefore we could plausibly have the first case evaluation be 3 so SQL moves to the second WHEN and re-evaluates… this time it calculates 1… then for the last WHEN it calculates 2… all that’s left is the default result of NULL.

Therefore be VERY careful if you use non-deterministic functions in a case statement!!! Not understanding how they work can cause some very unexpected and unwelcome results.
Comments:
NB: Comments will only appear once they have been moderated.