SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Recursion 100 has been exhausted
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stahorse
Yak Posting Veteran

85 Posts

Posted - 04/26/2013 :  08:47:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 04/26/2013 :  09:04:03  Show Profile  Reply with Quote
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;
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/27/2013 :  10:19:26  Show Profile  Reply with Quote
Excellent explanation!
Thanks James.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 04/28/2013 :  08:18:40  Show Profile  Reply with Quote
Thank you Madhu, for the kind words!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000