The maximum recursion 100 has been exhausted

Question

Post reply

The maximum recursion 100 has been exhausted

hoseam

SSCertifiable

Points: 5533
More actions
April 25, 2013 at 4:57 am

#277019

Hi
I have this query below, CTE code. and I get this error: The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
Can someone please look at the query and see what am I doing wrong and help.
Create TABLE #PR_LINK_INV_HST (CLIENT_ID varchar(15), NAME varchar(15), LINK_CLIENT_ID varchar(30))
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-23','John','1-14')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-14','Diana','1-23')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-23','John','1-33')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-33','Mary','1-23')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-17','Pope','1-19')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-19','Thabo','1-17')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-16','Smith','1-33')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-33','Mary','1-16')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-33','Mary','1-17')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-17','Pope','1-33')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-50','Josh','1-23')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-23','John','1-50')
DECLARE @CLIENT_ID VARCHAR(15)
SET @CLIENT_ID = '1-23'
-- This CTE search upwards for the linked client(child)
;WITH pr_linked(CLIENT_ID, NAME, LINK_CLIENT_ID, [LEVEL])
AS
(
SELECT CLIENT_ID, Name, LINK_CLIENT_ID, 1
FROM #PR_LINK_INV_HST
WHERE LINK_CLIENT_ID = @CLIENT_ID
UNION ALL
SELECT HST.CLIENT_ID, HST.Name, HST.LINK_CLIENT_ID, LNK.[LEVEL] + 1
FROM #PR_LINK_INV_HST HST
JOIN pr_linked LNK
ON LNK.CLIENT_ID = HST.LINK_CLIENT_ID
)
SELECT *
INTO #RESULTS
FROM pr_linked
--select * from #RESULTS
-- This CTE search upwards for the linked client(parent)
;WITH pr_linked(CLIENT_ID, NAME, LINK_CLIENT_ID, [LEVEL])
AS
(
SELECT CLIENT_ID, Name, LINK_CLIENT_ID, 0
FROM #PR_LINK_INV_HST
WHERE CLIENT_ID = @CLIENT_ID
UNION ALL
SELECT HST.CLIENT_ID, HST.Name, HST.LINK_CLIENT_ID, LNK.[LEVEL] - 1
FROM #PR_LINK_INV_HST HST
JOIN pr_linked LNK
ON HST.CLIENT_ID = LNK.LINK_CLIENT_ID
)
INSERT INTO #RESULTS
SELECT *
FROM pr_linked
-- display result
SELECT *
FROM #RESULTS
drop table #RESULTS
drop table #PR_LINK_INV_HST

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Use the MAXRECURSION hint:

SELECT columns

FROM recursivecte

OPTION (MAXRECURSION 0); -- unlimited

In practice you might want to set it to a sensible value then increment, JIC your CTE loops.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

David McKinney SSChampion Points: 10472 More actions · Answer 2

Not on my sql box right now but...

I think there's something circular going on....e.g. A reports to B, B reports to A

Can you check your data? Also have you got a STOP condition...the CTE will stop recursing when the lower part of the UNION returns no rows.

If you're always passing in a start Id, then your lower query should specify that recursion stops when your back with the start Id.

i.e. something like

WHERE linked_client_id <> @Client_id