maximum nesting level exceeded (level 32) error

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 2000 Forums

Transact-SQL (2000)

maximum nesting level exceeded (level 32) error

Author

Topic

vijayp
Starting Member

1 Post

Posted - 2008-11-11 : 02:27:05

Hi Experts,
I am working on a web based software for a Multi level Marketing Company where one member joins and then gets 2 members below him - one on the left and other the right leg.
For getting count for any member I have a stored procedure which calls user defined function. The user defined function calls the same function for getting the leg count of any member who logs in and wishes to see leg count.
Every thing works fine till a member reaches 950 members below him but after more than 950 members get registered below a member (on either leg) the nesting error is shown when a member clicks on one's leg count link provided to see leg count.
Please advise i have a deadline to meet..

Stored procedure is as follows:

CREATE Procedure GetLegCount_forBSGUser

@UserID int

AS

select count(*) as count from BSGGetReports3(1,@UserID) where status='Y'

GO

User defined function is:

CREATE FUNCTION dbo.BSGGetReports3(@IncludeParent bit, @UserID int)
RETURNS @retFindReports TABLE (UserID int, Name varchar(50), ReferID int, LegType varchar(1), Status varchar(1))
AS
BEGIN
IF (@IncludeParent=1)
BEGIN
INSERT INTO @retFindReports
SELECT userid,username,referid,PositionType,Status FROM BSGUserregister WHERE userID=@UserID
END

DECLARE @Report_ID int, @Report_Name varchar(50), @Report_BossID int, @Report_LegType varchar(1), @Report_Status varchar(1)

DECLARE RetrieveReports CURSOR STATIC LOCAL FOR

SELECT userid,username,referid,PositionType, Status FROM BSGUserRegister WHERE referID=@UserID union
SELECT userid,username,referid,PositionType, Status FROM BSGUserRegister WHERE parentID=@UserID

OPEN RetrieveReports

FETCH NEXT FROM RetrieveReports
INTO @Report_ID, @Report_Name, @Report_BossID,@Report_LegType, @Report_Status

WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @retFindReports
SELECT * FROM dbo.BSGGetReports3(0,@Report_ID)

INSERT INTO @retFindReports
VALUES(@Report_ID,@Report_Name, @Report_BossID, @Report_LegType,@Report_Status)

FETCH NEXT FROM RetrieveReports
INTO @Report_ID, @Report_Name, @Report_BossID, @Report_LegType,@Report_Status
END

CLOSE RetrieveReports
DEALLOCATE RetrieveReports

RETURN
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-11 : 03:18:11

try method in below link

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109492

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-11-11 : 08:05:41

quote:
Originally posted by vijayp

Hi Experts,
I am working on a web based software for a Multi level Marketing Company where one member joins and then gets 2 members below him - one on the left and other the right leg.

Great project. When you are done, maybe there is an international ring of puppy-killers that could use some database assistance.

If it is not practically useful, then it is practically useless.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources