Please Help in CTE (Common Table Expression)

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

General SQL Server Forums

New to SQL Server Programming

Please Help in CTE (Common Table Expression)

Author

Topic

vedjha
Posting Yak Master

228 Posts

Posted - 2009-06-12 : 04:33:16

I have Written a procedure ie,

"prctotalAssociatesinPools_level2"

When i execute this Procedure as:

prctotalAssociatesinPools_level2 'M1'

it gives an error as

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Procedure Structure:

create proc [prctotalAssociatesinPools_Level2] @sid varchar(9)
as
begin
if exists (select * from members where vaid=@sid)
begin

WITH CatTree (vaid,vuname, vspID,Sponsorname,cplacement, damtpaid, Level,dtdoj,cstatus)
AS
(
SELECT m.vaID,l.vuname, m.vspID,l1.vuname as Sponsorname,m.cplacement,damtpaid, 1 As [Level] , Convert(varchar(11),m.dtdoj,106 ) as dtdoj ,mem.cstatus
FROM memberstran m
join members mem on m.vaid=mem.vaid
join login l on m.vaid=l.vuid
join login l1 on m.vspid=l1.vuid

join packages p on mem.vpackid=p.vid
WHERE m.vspid=@sid

UNION ALL

SELECT C.vaid,l1.vuname, C.vspID, l2.vuname as Sponsorname ,C.cplacement,mem1.damtpaid, Level + 1 , Convert(varchar(11),c.dtdoj,106 ) as dtdoj,mem1.cstatus
FROM memberstran C
join members mem1 on C.vaid=mem1.vaid
JOIN CatTree ON
CatTree.vaid= C.vspid
join login l1 on C.vaid=l1.vuid
join login l2 on C.vspid=l2.vuid

join packages p1 on mem1.vpackid=p1.vid

)

SELECT count(vaid) as totalAssociatesinPools_Level2 FROM CatTree
where cstatus='V' and level=2
having count(vaid)>0

end

else

begin

print 'Please check your ID'

end

end

Please Help Me...

Ved Prakash Jha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-12 : 04:45:37

It means one of two things.

1. You have a circular reference in your structure
2. You do have a hierarchy of more than 100 levels

I'd investigate option #1 since this has a higher probability.

E 12°55'05.63"
N 56°04'39.26"

vedjha
Posting Yak Master

228 Posts

Posted - 2009-06-12 : 04:48:39

Yes structure having Circular Reference
and it has unlimited Level.

There is any Option to solve this Exception

Ved Prakash Jha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-12 : 04:52:45

You tell us what to do when circular reference is encountered.
It's your business rules, not ours.

See this topic how to deal and stop processing more records when encountering circular reference
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115290
http://weblogs.sqlteam.com/peterl/archive/2008/11/27/Expand-network-using-CTE-without-circular-reference.aspx

E 12°55'05.63"
N 56°04'39.26"

vedjha
Posting Yak Master

228 Posts

Posted - 2009-06-12 : 04:55:17

wait, i know its my job but
i m in problem . there is another way to to solve this Exception

Ved Prakash Jha

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-12 : 07:43:07

Follow the links peso posted.

They show a way to stop circular reference.

Peso is telling you that it is 100% *UP TO YOU* to decide what to do when in the case of circular reference. we can give you technical information but you have to decide the rules you want to follow in the case of circular reference.

-- Peso, I keep seeing situations now where that type of CTE can be useful. Cheers once again.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-12 : 07:45:45

You're welcome, Charlie!

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources