Recursion 100 has been exhausted

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Recursion 100 has been exhausted

Author

Topic

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-04-26 : 08:47:56

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-26 : 09:04:03

You have a circular loop - that is the reason for the infinite recursion. You have to do something to stop the recursion when you traverse the same node that you have already traversed. Below is one way for you to do this.

The example you posted is a simple loop; even more complex topologies can work. But:

1. if you had a topology with multiple loops (i.e., you could start from a given node and can get back to that node via more than one path), this would not work correctly - it would return results in duplicate; it will return all the nodes it should, but just that a given node would be returned more than once.

2. If you had loops connected via a non-loop, it would not pick up the connections in other loops. (think of two disjoint circular loops connected by a line).

For such problems, set-based T-SQL won't work. You would need to use procedural code.

DECLARE @CLIENT_ID VARCHAR(15)
SET @CLIENT_ID = '1' 

;WITH cte AS
(
	SELECT *, CAST(CLIENT_ID + '|' AS VARCHAR(MAX)) AS traversed
	FROM #PR_LINK_INV_HST WHERE @CLIENT_ID = CLIENT_ID
	UNION ALL
	SELECT t.*, CAST(traversed+t.client_id + '|' AS VARCHAR(MAX)) AS traversed
	FROM #PR_LINK_INV_HST t 
	INNER JOIN cte c ON c.LINK_CLIENT_ID = t.CLIENT_ID
	WHERE traversed NOT LIKE '%'+t.client_id + '%'
) SELECT * FROM cte;

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-27 : 10:19:26

Excellent explanation!
Thanks James.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-28 : 08:18:40

Thank you Madhu, for the kind words!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources