CTE Max Recursion

Msg 530, Level 16, State 1, Line 3

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

 To prevent infinite recursion, a default value for MAXRECURSION = 100 has been already set.
To change the maximum recursion, use this:

 

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);

Maximum No of recursion is 32767

SELECT * FROM CTE OPTION (MAXRECURSION 32768);

 

And it will throw this error

Msg 310, Level 15, State 1, Line 10

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

Source: here