Maximum nesting level of CASE statement

Did you know that the 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:

SELECT 
	ID,
	CASE WHEN ID =1 THEN 
	 CASE WHEN ID = 2 THEN 
	  CASE WHEN ID = 3 THEN 
	   CASE WHEN ID = 4 THEN 
	    CASE WHEN ID = 5 THEN 
	     CASE WHEN ID = 6 THEN 
	      CASE WHEN ID = 7 THEN 
	 	CASE WHEN ID = 8 THEN 
		 CASE WHEN ID = 9 THEN 
		  CASE WHEN ID = 10 THEN 1 ELSE NULL END
		 ELSE NULL END
	        ELSE NULL END
	       ELSE NULL END
	      ELSE NULL END
	     ELSE NULL END
	    ELSE NULL END
	   ELSE NULL END
	  ELSE NULL END
	ELSE NULL END Col1
FROM (VALUES ('1'), ('2'), ('3'), ('4'), ('5'),
	     ('6'), ('7'), ('8'), ('9'), ('10'), 
             ('11')) AS b (ID)

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:

SELECT 
	ID,
	CASE WHEN ID =1 THEN 
	 CASE WHEN ID = 2 THEN 
	  CASE WHEN ID = 3 THEN 
	   CASE WHEN ID = 4 THEN 
	    CASE WHEN ID = 5 THEN 
	     CASE WHEN ID = 6 THEN 
	      CASE WHEN ID = 7 THEN 
	       CASE WHEN ID = 8 THEN 
		CASE WHEN ID = 9 THEN 
		 CASE WHEN ID = 10 THEN
		   CASE WHEN ID = 11 THEN NULL ELSE NULL END
		  ELSE NULL END
		 ELSE NULL END
	        ELSE NULL END
	       ELSE NULL END
	      ELSE NULL END
	     ELSE NULL END
	    ELSE NULL END
	   ELSE NULL END
	  ELSE NULL END
	ELSE NULL END
FROM (VALUES ('1'), ('2'), ('3'), ('4'), ('5'),
             ('6'), ('7'), ('8'), ('9'), ('10'), 
 	     ('11')) AS b (ID)

This time an exception was fired clearly saying that the internal limit of a maximum of 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.