Stored procedure or function nesting level exceeded error (level 32)

Question

Stored procedure or function nesting level exceeded error (level 32)

@@VICTOR

SSC Enthusiast

Points: 121
More actions
November 11, 2008 at 12:17 am

#196180

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
@user-id int
AS
select count(*) as count from BSGGetReports3(1,@UserID) where status='Y'
GO
User defined function is:
CREATE FUNCTION dbo.BSGGetReports3(@IncludeParent bit, @user-id 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
And have already tried using CTE but seems it doesnt work on sql 2000. Please Advise....

Viewing 5 posts - 1 through 5 (of 5 total)

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

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 1

there is no CTE command in SQL 2000, it' available in SQL 2005 and above editions.

If you are using SQL 2008, see the link: http://msdn.microsoft.com/en-us/library/bb677173.aspx and your problem was resolved.

😎

@@VICTOR SSC Enthusiast Points: 121 More actions · Answer 2

yes the CTE doesnt work with Sql Server 2000. I need to change the cursor with something else...if you could suggest.

@@VICTOR SSC Enthusiast Points: 121 More actions · Answer 3

am also posting the code for database generation ...hope this helps me..

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#BSGUserregister','U') IS NOT NULL

DROP TABLE #BSGUserregister

--===== Create the test table with

CREATE TABLE #BSGUserregister

(

USERID INT,

UserName varchar(50),

ReferId INT,

PositionType varchar

INSERT INTO #BSGUserregister (USERID ,UserName ,ReferId ,PositionType )

SELECT '9999','RRS','0','M'

SELECT '10000','prs1','9999','L'

SELECT '10001','prs2','9999','R'

SELECT '10002','rvs1','10000','L'

SELECT '10003','rvs2','10000','R'

SELECT '10004','rvs3','10001','L'

SELECT '10005','rrs2','10001','R'

SELECT '10006','rrs2','10002','L'

SELECT '10007','rrs2','10002','R'

SELECT '10008','rrs2','10003','R'

Thanks

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Continued here:

http://www.sqlservercentral.com/Forums/FindPost601206.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass