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 AS INT = 150

;WITH CTE AS (

  SELECT 1 Num

  UNION ALL

  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…