Maximum stored procedure, function, trigger or view nesting level exceeded (Level 32)

Question

Maximum stored procedure, function, trigger or view nesting level exceeded (Level 32)

ajaykini

SSC Veteran

Points: 239
More actions
November 7, 2008 at 10:43 am

#195771

Hi Experts,
I have developed 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.
Being a newbie I request clear guidance as to how to resolve this since I have provide solution to client tomorrow.
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
Thanks in advance for all who send in their guidance

Viewing 15 posts - 1 through 15 (of 18 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

First, you don't really need to be using a cursor.

Second, read the article in my signature block below, and follow its advice and post additional information on this thread. It will help us help you. There is a better way to achieve what you are trying to do, we just need to know more about your database and data as it applies to this particular problem.

noeld SSC Guru Points: 96590 More actions · Answer 2

..... SELECT * FROM dbo.BSGGetReports3(0,@Report_ID)

Is this function supposed to be recursive ?

* Noel

ajaykini SSC Veteran Points: 239 More actions · Answer 3

Thank you Lynn

The db structure is:

Sample data would look like:

10000|rrs|rrs123|abc street, mumbai|9999|Left| 11-02-2008|9999|11-03-2008|9999|Bronze|0|N|

1

Hope i have provided the right data in a proper manner for you to understand

Thanks

ajaykini SSC Veteran Points: 239 More actions · Answer 4

ajaykini

SSC Veteran

Points: 239

November 7, 2008 at 11:32 am

#895744

Thanks Noel

Yes it's recursive

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

ajaykini (11/7/2008)
Thank you Lynn
The db structure is:
UserID | UserName | UserPwd|Address|ReferID|PositionType |regdate|SponsorID|UpdateDate|FranchiseeID|MemberType|ParentId|Reentry| Unit
Sample data would look like:
10000|rrs|rrs123|abc street, mumbai|9999|Left| 11-02-2008|9999|11-03-2008|9999|Bronze|0|N|
1
Hope i have provided the right data in a proper manner for you to understand
Thanks

Not really. You might want to reread the article. What you provided doesn't really help me help you. We really need the DDL for the tables, sample data that will allow use to test anything we write (sorru, one record really isn't enough).

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

A Cursor-based recursive function? Wow. That is going to be slow.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

ajaykini SSC Veteran Points: 239 More actions · Answer 7

ajaykini

SSC Veteran

Points: 239

November 7, 2008 at 10:56 pm

#895915

Barry:

What would you suggest instead?

Thanks

ajaykini SSC Veteran Points: 239 More actions · Answer 8

Lynn:

Thank you for your understanding and patience.

I hope i am now on the right track to receive your valuable advise to resolve the problem.

--===== 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: 1004504 More actions · Answer 9

ajaykini (11/7/2008)
Lynn:
Thank you for your understanding and patience.
I hope i am now on the right track to receive your valuable advise to resolve the problem.

Almost. For the sample data you posted, what do you want the procedure to return?

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Question: Your function is referencing a "Status" column that is not in your table definition?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

ajaykini (11/7/2008)
Barry:
What would you suggest instead?

An inline function that uses a recursive CTE instead. Like this:

CREATE FUNCTION dbo.BSGGetReports3( @IncludeParent bit, @user-id int )

RETURNS TABLE AS -- inline table-valued function:

Return (

--====== Use a recursive CTE to find all of the direct and indirect members of a User

WITH SubMembers As (

--Anchor branch Definition:

Select m.UserID, m.UserName, m.ReferID, m.PositionType, m.Status, 0 as Depth

From BSGUserregister m

Where UserID = @user-id--find the User

Union All

--Recursive branch Definition

Select m.UserID, m.UserName, m.ReferID, m.PositionType, m.Status, p.Depth+1 as Depth

From BSGUserregister m

-- Find all users whose Refer is already in SubMembers

Inner Join SubMembers p ON P.UserId = m.ReferID)

-- part of the CTE that executes the recursive defintion

SELECT

UserId, UserName, ReferID, PositionType, Status, Depth

From SubMembers

Where -- Enforce the @IncludeParent parameter

(@IncludeParent = 1) OR (UserId != @userid)

)

Note that recursive CTE's have a default MAXRECURSION of 100. If this is still not enough, you can raise it, pretty much as high as you need to by putting a Query Hint on your calling SELECT statment, like this:

Select * from dbo.BSGGetReports3(1,10000)

Option (MAXRECURSION 1000);--allow the recursive CTE to go 1000 levels deep

You cannot put this hint in the function itself, because it is only allowed on the highest-level SELECT statement.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

ajaykini SSC Veteran Points: 239 More actions · Answer 12

Well as i mentioned after a particular member gets more than 950 members below the nesting error is seen when leg count is link is activated.

Please advise what needs to be done to resolve this

Thanks

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

ajaykini (11/8/2008)
Well as i mentioned after a particular member gets more than 950 members below the nesting error is seen when leg count is link is activated.
Please advise what needs to be done to resolve this
Thanks

I just posted the solution for you. Please try it first before reposting.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

ajaykini SSC Veteran Points: 239 More actions · Answer 14

Barry:

CTE works in sql 2005 and not in sql 2000 i guess.

I am having sql 2k as back end.

What is your advise