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. Yes, you can, and the MAXRECURSION option is the way to do it.

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.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.