Prevent recursive CTE from entering an infinite loop

Common Table Expression (CTE) is not a new feature now. It has been around with the launch of SQL Server 2005. Since then, we might have come across many powerful examples of using CTE and have made the most out of it. The best use of CTE I take is through recursion. We can create recursive CTE to get our job done very easily in situations where we need to repeatedly refer a table/view.

The most common situation in which developers land up is an infinite loop for a badly build CTE. So, to counter this situation, we can make use of MAXRECURSION option.

Let’s give it a hands on..

USE AdventureWorks

GO

;WITH EmpCTE

AS

(

  SELECT

    EmployeeID, ManagerID

  FROM

    HumanResources.Employee

  WHERE

    ManagerID IS NULL

  UNION ALL

  SELECT

    e.EmployeeID, e.ManagerID

  FROM

    HumanResources.Employee e

  INNER JOIN

    EmpCTE cte

  ON cte.EmployeeID = e.ManagerID

)

SELECT * FROM EmpCTE OPTION (MAXRECURSION 4)

GO

Now if your CTE goes beyond 4th recursion it will throw an error and stop executing. And if you happen put MAXRECURSION value too low, it might be possible that before your desired result is accomplished it 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 short, MAXRECUSION is good way to prevent recursive CTE to enter an infinite loop.