quote:

---

G. Using MAXRECURSION to cancel a statement
MAXRECURSION can be used to prevent a poorly formed recursive CTE from entering into an infinite loop. The following example intentionally creates an infinite loop and uses the MAXRECURSION hint to limit the number of recursion levels to two.

Copy Code
USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
SELECT EmployeeID, ManagerID, Title
FROM HumanResources.Employee
WHERE ManagerID IS NOT NULL
UNION ALL
SELECT cte.EmployeeID, cte.ManagerID, cte.Title
FROM cte
JOIN HumanResources.Employee AS e
ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO


After the coding error is corrected, MAXRECURSION is no longer required. The following example shows the corrected code.

---

DECLARE @Geneology TABLE (
	[personID] INT PRIMARY KEY
	, [surname] VARCHAR(255)
	, [firstname] VARCHAR(255)
	, [parentID] INT
	)

INSERT @Geneology ([personID], [surname], [firstname], [parentID])
      SELECT 1, 'Smith', 'Fred', NULL -- Fred Smith
UNION SELECT 2, 'Smith', 'David', 1 -- David, Son of Fred
UNION SELECT 3, 'Smith', 'Judith', 2 -- Judith, Daughter of David
UNION SELECT 4, 'Jones', 'Mary', 3 -- Mary, Daughter of Judith
UNION SELECT 10, 'Brown', 'Harry' , NULL -- Harry Brown
UNION SELECT 11, 'Brown', 'Charles', 10 -- Charles, Son of Harry

-- Family Tree CTE (simplified)
; WITH family (
	[personId]
	, [Surname]
	, [Firstname]
	, [paternaty Path]
	, [parentID]
	, [level]
	)
AS (
	-- Anchor Definition (Gets the starts of all families where the parent is not known)
	SELECT
		[personID]
		, [surname]
		, [firstname]
		, CAST([firstname] + ' ' + [surname] AS VARCHAR(MAX))
		, [parentID]
		, 1
	FROM
		@geneology
	WHERE
		[parentID] IS NULL
	-- Recursive Definition
	UNION ALL SELECT
		g.[personID]
		, g.[surname]
		, g.[firstname]
		, f.[paternaty Path] + ' -> ' + CAST(g.[firstname] + ' ' + g.[surname] AS VARCHAR(MAX))
		, g.[parentID]
		, f.[level] + 1
	FROM
		@geneology g
		JOIN family f ON f.[personID] = g.[parentID]
	)
SELECT * FROM family OPTION (MAXRECURSION 0)