SQL SERVER – SQL SERVER – Simple Example of Recursive CTE – Part 2 – MAXRECURSION – Prevent CTE Infinite Loop

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.

USE AdventureWorks
GO
WITH Emp_CTE AS (
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
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
)
SELECT *
FROM Emp_CTE OPTION (MAXRECURSION 5)
GO

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)

Solarwinds
, ,
Previous Post
SQL SERVER – Simple Example of Recursive CTE
Next Post
SQLAuthority News – Author BirthDay – SQL Server Birthday

Related Posts

26 Comments. Leave new

  • is there any way you can set CTE Maxrecurssion at dataabse /server level rather than query level

    Reply

Leave a Reply

Menu