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)
26 Comments. Leave new
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
I tried this example, but it didn’t go into the recursive statements. I modified it to parse the entire string and return each element of the string.
DECLARE @txt as nvarchar(100)
SET @txt = ‘Banana;Chocolate;Milk;Bread;’;
WITH ParseText( Ltxt, Rtxt) AS (
SELECT
Ltxt = LEFT (@txt, CHARINDEX( ‘;’, @txt)-1 )
,Rtxt = RIGHT(@txt, LEN(@txt) – CHARINDEX( ‘;’,@txt) )
UNION ALL
SELECT
Ltxt = LEFT(tn.Rtxt, CHARINDEX( ‘;’,tn.Rtxt)-1 )
,Rtxt = RIGHT(tn.Rtxt, LEN(tn.Rtxt) – CHARINDEX( ‘;’,tn.Rtxt) )
FROM ParseText tn
where Rtxt ”
)
SELECT
Ltxt = CASE WHEN Ltxt = ” THEN RTxt ELSE Ltxt END
FROM ParseText
Btw, you need to replace the slanted single quotes to match the straight single quotes if you try to copy and paste the query into SSMS.
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
Hi Pinal,
How can we avoid the error
“Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 3 has been exhausted before statement completion. ”
if cte goes above 100 levels, then what need to do?
You need to set it to 0
Hi Paresh,
Set the MAXRECURSION hint of CTE query to any nonnegative value between 0 and 32767 as below:
SELECT EmployeeID, ManagerID, Title
FROM cteEmployee
OPTION (MAXRECURSION 200);
When 0 is specified, no limit is applied. If this option is not specified, the default limit is 100.
Regards,
Pinal Dave
Hello Pinal,
It is really working.
Thanks a lot.
Hello Pinal Dave,
I have a problem with MAXRECURSION hint of CTE query
below
declare @id int
set @id=1;
with cte(member_id,lft,rgt) as (
select member_id,0,0
from [MultilevelChain].[dbo].[member_info]
where member_id=@id
union all
select t.member_id,1-t.position, t.position
from [MultilevelChain].[dbo].[member_info] t
inner join cte c on c.member_id = t.sponser_id)
select sum(lft) as LeftNode,
sum(rgt) as RightNode
from cte
option (maxrecursion 32767)
it gives error message like
Msg 530, Level 16, State 1, Line 5
The statement terminated. The maximum recursion 32767 has been exhausted before statement completion.
Here all below levels are searched but problem with top levelit has attach with 6 node under it. all levels are ok but not show id as 1 pls help me. i waitig to ur reply
Tanks regards
Niranjan Singh Pune
I’m creating a .net cms application. I want a user to be able to build a menu by select multiple parent menu items. Each menu item can have two levels of inheritance.
Using cte I can get the tree structure for the menu, however I want to be able to insert the tree structure with positioning. As users with also be able to change the positioning of the menu items.
As under the news sub menu World news should be before UK news.
Any help and guidance will be greatly appreciated.
Is this even possible using CTE?
DECLARE @menu TABLE
(
id int, parentId int, title varchar(128)
)
INSERT @menu
SELECT 1,NULL,’Home Page’ UNION ALL
SELECT 2,1,’News’ UNION ALL
SELECT 3,2,’World’ UNION ALL
SELECT 4,2,’UK’ UNION ALL
SELECT 5,2,’Business’ UNION ALL
SELECT 6,1,’Sports’ UNION ALL
SELECT 7,6, ‘Football’ UNION ALL
SELECT 8,6,’Tennis’ UNION ALL
SELECT 9,6, ‘Formula One’ UNION ALL
SELECT 10,6, ‘Boxing’ UNION ALL
SELECT 11,2, ‘Politics’ UNION ALL
SELECT 12,6, ‘Cricket’
;WITH MenuTree AS
(
SELECT
M.id,
M.parentId,
M.title,
0 AS menuLevel,
Cast(M.title + ‘\\’ AS varchar(max)) AS treePath,
null AS position
FROM
@menu AS M
WHERE
M.id = 1
UNION ALL
SELECT
M.id,
M.parentId,
M.title,
menuLevel + 1,
Cast(MenuTree.treePath + M.title + ‘\\’ AS varchar(max)) AS treePath,
null AS position
FROM @menu AS M
INNER JOIN MenuTree ON M.ParentID = MenuTree.id
)
SELECT * FROM menuTree order by treePath
how do i get the positioning…
id – title – position
1 Home Page – 1
2 News – 1
5 Business – 2
11 Politics – 3
4 UK – 4
3 World – 5
6 Sports – 2
10 Boxing – 1
12 Cricket – 2
7 Football – 3
9 Formula One – 4
8 Tennis -5
Thanks for your time.
I was hoping to restrict the recursion without throwing an error. Is there a way to get it to just stop at lets say 5 levels deep without throwing an error?
Well, of course. Just keep a counter which tells how deep in the recursion you are and stop there. For example like the following (which is taken and modified from example in my blog).
Take notice that ‘lvl’ field there which tells how deep we are. In recursion part I check that lvl < 2 and recursion stops there if it ever goes so deep.
WITH
cte(emp_id, emp_name, sup_id, sup_name, lvl) AS
(
SELECT e.emp_id, e.emp_name, s.emp_id, s.emp_name, 1
FROM t_employee e
LEFT OUTER JOIN t_employee s ON s.emp_id = e.supervisor_id
),
cte_start(emp_id, emp_name, sup_id, sup_name, lvl) AS
(
SELECT e.emp_id, e.emp_name, s.emp_id, s.emp_name, 1
FROM t_employee e
JOIN t_employee s ON s.emp_id = e.supervisor_id
LEFT OUTER JOIN t_employee sub ON sub.supervisor_id = e.emp_id
WHERE sub.emp_id IS NULL
),
cte_recur(emp_id, emp_name, sup_id, sup_name, lvl) AS
(
SELECT emp_id, emp_name, sup_id, sup_name, lvl
FROM cte_start
UNION ALL
SELECT c.emp_id, c.emp_name, c.sup_id, c.sup_name, r.lvl + 1
FROM cte c
JOIN cte_recur r ON r.sup_id = c.emp_id
where r.lvl < 2
)
SELECT DISTINCT emp_name 'Employee name', sup_name 'Supervisor name', lvl
FROM cte_recur
I think you can set a where clause on the level.
UNION ALL
SELECT
whatever…
WHERE
menulevel < 5
Hi there!
I need help with recursive operation like
Parents 1
———Chield1
———Chield2
——Sub -Chield1
——Sub -Chield2
Parents 2
Parents3
———Chield1
———Chield2
I want to display data exactly like above example with ‘-‘ operator.
Please provide an query.
thanks & regards
Brijesh Shah
Hi pinaldave
It’s great article and very helpful.
I’ve question.
I’ve sub group and trying to get all the parents so I tried this:
==================================
WITH Groups_CTE AS (
SELECT SubG.ParentID,SubG.Name FROM [Groups] AS SubG WHERE SubG.ID = 1
UNION ALL
SELECT ParentG.ID,ParentG.Name FROM Groups AS ParentG INNER JOIN Groups_CTE ecte ON ecte.ParentID = ParentG.ID
)
SELECT * FROM Groups_CTE
==================================
But it didn’t work, it thow exception says:
“The statement terminated. The maximum recursion 100 has been exhausted before statement completion.”
What should I do to make it work properly ?
Thanx
It mentioned that “Now if your CTE goes beyond 5th recursion it will throw an error and stop executing.” can you please elaborate a bit as I have made max recursion 200 but didn’t get any error..thnx
@Prakash,
there is mention “OPTION (MAXRECURSION 5)” so it will throw error beyond 5th recursion, while you have made max recursion to 200, so it will throw error after 200th recursion..
I have a CTE Table Category and Sub Category With ID,Parent ID relation.
I Wana to retrieve all Nested Sub Categories In Nested Ordered Manner.
Some one Help me soon The Result Shuld be in Following Manners.
Main Cat
— Sub A of Main
—-Child Sub A of Sub A of Main
—-Child Sub B of Sub A of Main
—-Child Sub C of Sub A of Main
— Sub B of Main
—-Child Sub A of Sub B of Main
—-Child Sub B of Sub B of Main
—-Child Sub C of Sub B of Main
— Sub C of Main
—-Child Sub A of Sub C of Main
—-Child Sub B of Sub C of Main
—-Child Sub C of Sub C of Main
It is very good example for starters. Thank you for posting!