In my last blog about Tally Tables, we talked about the use of
recursive CTEs to generate Tally Tables. Following that, someone quickly asked
me to generate a Tally Table for integers starting from 1 to 150 as when he
tried the way explained in my last post, the following exception was generated
–

Msg 530, Level 16,
State 1, Line 3

The
statement terminated. The maximum recursion 100 has been exhausted before
statement completion.

Now what to do?

Actually, to prevent infinite
recursion, a default value for MAXRECURSION
= 100 has been already set. Hence, any recursion will stop on reaching the
threshold limit. If we want to loop/iterate more than the default value, we
need to set the MAXRECURSION value as explained in my another post - Prevent recursive CTE from entering an infinite
loop

So, the
following statement will work to generate a Tally Table from 1 to 150–

DECLARE @Max ASINT= 150

;WITH CTE AS (

SELECT 1 Num

UNIONALL

SELECT Num + 1 FROM CTE WHERE Num < @Max

)

SELECT*FROM CTE OPTION (MAXRECURSION
150);

After looking at this
option, most of us will ask – what is the max value that could be used with the
MAXRECURSION option? And the answer is – 32767.
If we try to set a value greater than this,

SELECT*FROM CTE OPTION (MAXRECURSION
32768);

sql fires the following
exception –

Msg 310, Level 15,
State 1, Line 10

The value 32768
specified for the MAXRECURSION option exceeds the allowed maximum of 32767.

Hope, this post will make
you understand few more facts related to the use of CTE with recursion.

Happy
iterating…

Comments

Posted by jbreffni on 10 June 2011

Typo, line should say:

SELECT * FROM CTE OPTION (MAXRECURSION 32768);

Posted by alexms_2001 on 10 June 2011

On max depth: if MAXRECURSION is set to 0, there is no limit at all, so that the following works: