Yesterday I wrote about SQL SERVER – SQL SERVER – Simple Example of Recursive CTE. I right away received email from regular reader John Mildred that if I can prevent infinite recursion of CTE.
Sure! recursion can be limited. Use the option of MAXRECURSION.
WITH Emp_CTE AS (
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
WHERE ManagerID IS NULL
SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
FROM HumanResources.Employee e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
FROM Emp_CTE OPTION (MAXRECURSION 5)
Now if your CTE goes beyond 5th recursion it will throw an error and stop executing. If you put MAXRECURSION value too low it may be possible before your desire result is accomplished and will throw an error.
For example if you change MAXRECURSION to value 3. It will throw following error.
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 3 has been exhausted before statement completion.
In summary MAXRECUSION is good way to protect your CTE to go into infinite loop.
Reference : Pinal Dave (https://blog.sqlauthority.com)
is there any way you can set CTE Maxrecurssion at dataabse /server level rather than query level