Recursion 100 has been exhausted

Question

Post reply

Recursion 100 has been exhausted

hoseam

SSCertifiable

Points: 5533
More actions
April 26, 2013 at 6:47 am

#394677

Hi
I have a query below and I get The maximum recursion 100 has been exhausted, how do I fix or go on around it, while the while loop be a better escape?
--create PR_LINK_INV_HST temp table--
Create TABLE #PR_LINK_INV_HST (CLIENT_ID varchar(15), NAME varchar(15), LINK_CLIENT_ID varchar(30))
--insert into PR_LINK_INV_HST temp table--
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1','John','2')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('2','John','3')
Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('3','Diana','1')
DECLARE @CLIENT_ID VARCHAR(15)
SET @CLIENT_ID = '1'
-- This CTE search for the linked clients --
;WITH pr_linked(CLIENT_ID, NAME, LINK_CLIENT_ID, [LEVEL])
AS
(
/* Anchor member - the selected client*/
SELECT CLIENT_ID, Name, LINK_CLIENT_ID, 0
FROM #PR_LINK_INV_HST
WHERE CLIENT_ID = @CLIENT_ID
/* Recursive member to search for the child clients. */
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.LINK_CLIENT_ID = HST.CLIENT_ID
where
lnk.LEVEL >= 0
/* Recursive member to search for the parent clients. */
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
where lnk.LEVEL <= 0
)
SELECT distinct *
INTO #RESULTS
FROM pr_linked
-- display result
SELECT *
FROM #RESULTS
order by LEVEL, NAME
drop table #RESULTS
drop table #PR_LINK_INV_HST

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

OPTION(MAXRECURSION 0)

100 is the default value. 0 = max limit.

^{“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

hoseam SSCertifiable Points: 5533 More actions · Answer 2

with OPTION(MAXRECURSION 0) it just runs forever, I think I have aa circular loop somewhere and need to stop it, I just haven't found out yet how to.

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

hoseam (4/26/2013)
with OPTION(MAXRECURSION 0) it just runs forever, I think I have aa circular loop somewhere and need to stop it, I just haven't found out yet how to.

Set the limiter to a reasonable value, say 500, and examine the output.

^{“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

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

Comment out the INTO as below:

SELECT distinct *

--INTO #RESULTS

FROM pr_linked

^{“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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Start with your original post and the three sample records you provided. Does this recurse forever?

What is your expected results based on those three sample records?

Eric Nordlander SSCommitted Points: 1579 More actions · Answer 6

Lynn Pettis (4/26/2013)
Start with your original post and the three sample records you provided. Does this recurse forever?
What is your expected results based on those three sample records?

Lynn is right, it's your data. just for fun try this

truncate table #PR_LINK_INV_HST

----insert into PR_LINK_INV_HST temp table--

Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1','John','2')

Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('2','John','3')

Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('3','Diana',null)