CASE of recursion with SQL Server January 26th, 2012

Vinod Kumar

I have been waiting to write something on SQL Server, but never got a chance in the recent past. As part of preparation for the course for Pluralsight, we got few nice trivia’s around but missed on few of them to be included as part of the course. With TechEd India 2012 coming soon, plans to learn something interesting with SQL is always there – keep tuned in.

The maximum nesting levels with SQL Server has been with 32 levels and these have been there for a long time. While using stored procedures you can use the global variable @@NESTLEVEL to check the nested level at runtime for the nesting level. Even in the case of nested triggers this 32 limit still holds.

One exception comes with the recursion function inside SQL Server will be the use of CTE (Common table expression), but this can be limited upto 32767 value with the OPTION parameter. The documentation though says it can get into infinite loop :).

Now if you thought these were enough variations to nesting, here goes another with the CASE statement which is a limit of 10 nesting in a single query. Else you will get –

Msg 125, Level 15, State 4, Line 13
Case expressions may only be nested to level 10.

Now that is interesting and you can get it with a simple code that I used –

Declare @str Varchar(20) = ‘12345678901’
Select
CASE WHEN @str like ‘1%’
THEN CASE WHEN @str like ‘12%’
THEN CASE WHEN @str like ‘123%’
THEN CASE WHEN @str like ‘1234%’
THEN CASE WHEN @str like ‘12345%’
THEN CASE WHEN @str like ‘123456%’
THEN CASE WHEN @str like ‘1234567%’
THEN CASE WHEN @str like ‘12345678%’
THEN CASE WHEN @str like ‘123456789%’
THEN CASE WHEN @str like ‘1234567890%’
THEN CASE WHEN @str like ‘12345678901%’
THEN 1
END END END END END END END END END END END

Interestingly, the new IIF command with SQL Server 2012 can also be nested. So why don’t you try what is the nesting level we can have with the IIF command?

Happy learning and sharing !!! Feel free to drop your comments. Thanks.

Tags: , , , , ,

This entry was posted on Thursday, January 26th, 2012 at 07:15 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


3 Responses to “CASE of recursion with SQL Server”

  1. Just checked MSDN (http://msdn.microsoft.com/en-us/library/hh213574(v=SQL.110).aspx) and also confirmed via code that the same limits apply to IIF also.

  2. I believe IIF internally uses CASE and therefore, should be subjected to the same limitations as CASE. I will run a quick test later today and update.

Leave a Reply