error:Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)?

Question

1.00/5 (2 votes)

See more:

My page is run many times correctly.But now this error is appear:

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

i have a function in it that works well.

i dont have any stored procedure or trigger.

i dont know why this happenes!!

alter FUNCTION [dbo].[groupPath](@PostGroupID bigint) 
RETURNS nvarchar(100)
AS
BEGIN
declare @parent bigint 	
declare @ReturnPath nvarchar(100)
set @parent=(select PostGroupParentID from tblBase_PostGroup where PostGroupID=@PostGroupID)
if(@parent <>-1)
begin 
set @ReturnPath=(dbo.groupPath(@parent))
set @ReturnPath=@ReturnPath+'  »  '+(Select PostGroupName from tblBase_PostGroup where PostGroupID=@PostGroupID)
end
else
set @ReturnPath=(select PostGroupName from tblBase_PostGroup where PostGroupID=@PostGroupID)
return @ReturnPath
END;

Posted 11-Apr-12 19:15pm

maliheSadat

Updated 11-Apr-12 19:56pm

v3

Add a Solution

Comments

Ankur^\m/ 12-Apr-12 2:11am

I would suggest, add a breakpoint and debug the query. See why it is nesting so deep.

5 solutions

Solution 1

As the error message says, the nesting level of the function has exceeded the maximum limit allowed. You will have to analyse your function why it is so deeply nested. There are some good discussions here[^].
If you are still not able to solve the issue, you will have to post the code here.

Posted 11-Apr-12 19:24pm

Ankur^\m/

Comments

Uday P.Singh 12-Apr-12 1:38am

Correct 5!

Solution 2

The error means you're calling a procedure, view or function from your procedure, and then from that proc calling another, etc 32 levels deep. Or it could be recursive with the proc calling itself.

Either way, you're going to have to track down where that is coming from and change the code of your function so that the nesting level isn't anywhere close to that deep. There's no way to change the maximum nesting level.

For more information Read This[^]

hope it helps :)

Posted 11-Apr-12 19:37pm

Uday P.Singh

Comments

Ankur^\m/ 12-Apr-12 2:07am

Man, that is copied word-to-word from http://www.sqlservercentral.com/Forums/Topic642288-9-1.aspx#_ctl5_ctlTopic_ctlPanelBar_ctlTopicsRepeater__ctl13_lblFullMessage.
Please quote a reference when you do that or it will be cartogerized as plagiarized.

Solution 5

SQL

User Go keyword between exec procedure and final end
or simply alter only procedure not exec procedure command

Posted 12-Nov-14 21:17pm

Shashi Shanker singh

Solution 4

ALTER your database for RECURSIVE_TRIGGERS.

Quote:
If nested triggers are allowed and a trigger in the chain starts an infinite loop, the nesting level is exceeded and the trigger terminates. That time you do get this error. so simply rune this query.

SQL

USE yourdatabase
 GO
    -- Turn recursive triggers OFF in the database.
      ALTER DATABASE yourdatabase
      SET RECURSIVE_TRIGGERS OFF
GO

Hope your problem will resolve.

Posted 7-May-14 20:43pm

Pradeep Atkari

Comments

CHill60 8-May-14 4:23am

OP posted that it was the data at fault ... 2 years ago

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

maliheSadat · Accepted Answer · 2012-04-22T00:58:00

Solution 3

The problem was not in Function.It was in my data that i entered.
In a record ,the PostGroupParentID field and PostGroupID were the same Number!

so the loop was happened!
I change the PostGroupParentID ,then the problem was solved!

Posted 22-Apr-12 0:58am

maliheSadat

Updated 22-Apr-12 1:01am

v2