Maximum nesting level of CASE statement

Did you know that CASE statement has a maximum nesting level of 10? The same limit exists for IIF() function which is just a syntactical sugar and is internally transformed into multiple CASE statements. I have prepared two samples you can play with it.

Let’s start with the query that is still working because the limit isn’t hit:

Don’t be surprised by the fact that we have 11 rows in the result set. We have the first CASE statement and then another 10 nested inside. You can see that the maximum ID we have used to check by CASE was 10.

Let’s go and modify the query to include another nested CASE statement for ID = 11:

This time an exception was fired clearly saying that the internal limit of maximum 10 nested CASE statements was reached. So be careful and remember this limitation in your development especially when working with dynamically generated SQL where this error can occur only at specific data in the input.

For further reading, I will strongly recommend the article Dirty Secrets of the CASE Expression written by Aaron Bertrand for https://sqlperformance.com, mainly the section Simple CASE expressions can become nested over linked servers.

Leave a Reply

Your email address will not be published. Required fields are marked *