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 (http://blog.SQLAuthority.com)
pinaldave
Thanks for this and your previous examples regarding recursion on SQL server. I was fond of oracle’s connect by, and sad to see that it didn’t exist in SQL Server. This did the trick, though! Thanks again!
It’s easy to parse delimited string with WITH statement
DECLARE @txt as nvarchar(100)
SET @txt = ‘Banana;Chocolote;Milk;Bread’
;WITH ParseText( Ltxt, Rtxt) AS (
SELECT –anchor member
Ltxt = LEFT (@txt, CHARINDEX( ‘;’, @txt) )
,Rtxt = RIGHT(@txt, LEN(@txt) – CHARINDEX( ‘;’,@txt) )
UNION ALL –Recursive member
SELECT
Ltxt = LEFT(tn.Rtxt, CHARINDEX( ‘;’,tn.Rtxt) )
,Rtxt = RIGHT(tn.Rtxt, LEN(tn.Rtxt) – CHARINDEX( ‘;’,tn.Rtxt) )
FROM ParseText tn where Ltxt ” — Recursive loop
)
SELECT
Ltxt = CASE WHEN Ltxt = ” THEN RTxt ELSE Ltxt END
FROM ParseText
Hi Pinal,
Thanks for the post on CTE. Still I am not clear how can we restrict the recursion in CTE. Ex. The normal select statement (without MAXRECURSION) returns 10 records. If we give OPTION (MAXRECURSION 3), the query will throw error. If we give MAXRECURSION 4 or above it gives all the 10 records which is same as query without MAXRECURSION. Could you please clarify how can it restrict the recursion to say 3?
@Prasant
Please provide an example query.
Hi pinal,
Thank you for the content, i was looking for the same.
Hi Pinal,
I am student learning SQL server. I actaully didnot know what exactly maxrecursion function do?
can you please be precise.
Thanks&Regards,
Raghuveer.
@Raghuveer
A recursive CTE can potentially go on forever. So, SQL Server limits it to 100 cycles. MAXRECURSION allows you to change that limit between 1 and 32767 or 0 which means unlimited.
Hi there!
I need help with recursive operation
I do have 2 tables
#1 Inspections
ID
PARENT_ID
POSITION
#2 Resources
RESOURCES_ID
CULTURE_LANGUAGE
CAPTION
DATA SAMPLE (OUTPUT)
SELECT a.id, a.parent_id, a.position, b.culture_language, b.caption
FROM Inspections AS a
INNER JOIN InspectionsResources AS b ON
a.id = b.resource_id where culture_language=’en’
VEHICLES ID = 1, PARENT_ID = 0, POSITION=1
FORKLIFTS ID = 2, PARENT_ID = 0, POSITION=2
CARS ID = 3, PARENT_ID = 1, POSITION=1
MOTORCYCLES ID = 4, PARENT_ID = 1, POSITION=2
DOORS ID = 5, PARENT_ID = 3, POSITION=1
HANDLES ID = 6, PARENT_ID = 3, POSITION=2
…
I need to load all data for CARS only!!
Thank you Pinal
Alain