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

Question

I am creating a store procedure but while executing the procedure i am getting the particular error.

Msg 217, Level 16, State 1, Procedure SendMail_Renewapp, Line 77 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Can anyone please help me out from this problem.

My procedure is as follows..

`ALTER PROCEDURE [dbo].[SendMail_Renewapp] 
-- Add the parameters for the stored procedure here

AS
BEGIN
declare @xml nvarchar(max)
declare @body nvarchar(max)
declare @currentdate datetime;
declare @ExpDate datetime;
declare @mailsendingdate datetime;
declare @renewtime varchar(10);
    DECLARE @AgencyId int;
DECLARE @ApplicationID int;
declare @emailid varchar(100);

set @currentdate=getdate();


                --Fetching the application details: start--
                DECLARE AppCursor CURSOR FOR 
                Select top 5 applications.ap_id,applications.ap_expiry_date,agency.ag_co_email from applications  join agency on applications.ap_agency_id=agency.ag_id
                 where ap_status='AS' and ap_iame_flag='IA' and ap_expiry_date != '' 
                    OPEN AppCursor
                    FETCH NEXT FROM AppCursor INTO @ApplicationID,@ExpDate,@emailid

                    WHILE @@FETCH_STATUS = 0 
                    BEGIN

                     SET @renewtime = ABS(DATEDIFF(day, @currentdate, @ExpDate))
                            if(@renewtime=180)

                                BEGIN

                                    --SET @xml = CAST(( SELECT [ag_id] AS 'td','',[ag_name] AS 'td','',[ag_co_email] AS 'td','',[ag_mobile] AS 'td'FROM  beesl.dbo.Agency where @renewtime < 180
--FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html>
<body>
   <div>
<div>
<H3>Agencies Details whose payment are still pending for last 3 months</H3>
</div>
<table cellpadding="4" cellspacing="1" bgcolor=#FFFFFF   border=1 rules=none frame=box  > 
<tr  >
<th style=border:1px solid #000000;  align=left bgcolor=#c2c2c2> Agency ID </th>
 <th style=border:1px solid #000000;  align=left bgcolor=#c2c2c2> Agency Name </th> 
<th style=border:1px solid #000000;   align=left bgcolor=#c2c2c2> Agency Email </th> 
<th style=border:1px solid #000000;   align=left bgcolor=#c2c2c2> Contact Number </th> 

</tr>'   
    SET @body = @body + @xml +'</table></div></body></html>'

EXEC msdb.dbo.sp_send_dbmail 
@profile_name='BEE', 
@recipients='emailid@emailid.com', 
@subject='Renew Applications',
--@file_attachments = 'D:\beelogo.png',
@importance= High,
--@body = 'Testing'
@body = @body,
@body_format ='HTML';

                                END


                    FETCH NEXT FROM AppCursor INTO      @ApplicationID,@ExpDate,@emailid
                END
                    CLOSE AppCursor
                    DEALLOCATE AppCursor
                --Fetching the application details: end--


  END`

http://stackoverflow.com/questions/6615327/what-leads-to-this-strange-sql-behavior — Squirrel5853, Aug 22 '13 at 12:10

score 47 · Answer 1 · answered Oct 30 '15 at 16:55

47

Use the "Go" after the END statement

answered Oct 30 '15 at 16:55

user2936035

495
4
2

12

Please explain your answer. – qxg Nov 01 '15 at 05:48
2

SO have lots of END statement (so am I), but where exactly should I put the GO? At the very bottom(aka the last END statement) or in every SP I call inside an IF-statement? – Jack Frost Dec 16 '16 at 00:58
Yes, this works -- but I'm completely flabbergasted. Thanks – Phil Whittington Nov 05 '18 at 16:48

score 32 · Answer 2 · answered Jan 25 '17 at 14:39

32

The procedure is created with an EXEC of itself inside it. Therefore, a GO must be placed before the EXEC so the procedure will be Created/Altered before getting executed. Thus, avoiding the RECURSION.

answered Jan 25 '17 at 14:39

jmag

796
1
8
17

4

I forgot to comment out my "test" EXEC inside of my stored procedure. This was exactly my problem, thank you! – hackingchemist Feb 08 '18 at 22:51

score 13 · Answer 3 · edited Aug 24 '18 at 01:46

Check trigger nesting level right at the beginning of the trigger by using TRIGGER_NESTLEVEL function and stop the trigger to perform action if the trigger level is greater than 1.

 IF TRIGGER_NESTLEVEL() > 1
     RETURN

The error is occurring due to the nesting level exceeding its limit, because we all know that trigger continuously fires and its difficult to control that behavior of the trigger. The function TRIGGER_NESTLEVEL returns the nesting level and we can stop the nesting level to increase.

score 5 · Answer 4 · answered Aug 22 '13 at 12:28

5

Remove the BEGIN and END for your IF statement

example

WHILE @@FETCH_STATUS = 0
BEGIN

    IF @variable
    --NO BEGIN
       --Do this
    --NO END
END

answered Aug 22 '13 at 12:28

Squirrel5853

2,376
1
18
34

That worked for me. Thanks a lot!! Can you tell me what difference "BEGIN END" makes ? – VPP Aug 25 '15 at 18:07
1

To be honest no. I would usually use BEGIN - END to wrap any block of logic sort of like the curly braces within c# "{ }". I found [this question](http://stackoverflow.com/questions/1180279/when-do-i-need-to-use-begin-end-blocks-and-the-go-keyword-in-sql-server) which I think answers that – Squirrel5853 Aug 26 '15 at 08:46

score 5 · Answer 5 · answered Jun 10 '20 at 21:21

5

Use

RETURN

at the end of the procedure

answered Jun 10 '20 at 21:21

Selvin Medina

51
1
3

score 2 · Answer 6 · answered May 21 '20 at 17:20

Ok, So this one is pretty old, so I figured I would provide the correct answer. You should add SET NOCOUNT ON at the top of the stored procedure and SET NOCOUNT OFF before you attempt to return a result (your final select).

Without this statement, your execution will treat every select statement as a result to output. When an external ADO or ADO.NET attempts to call the stored procedure and get a result, you will get the "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)" message. Its the select statement for your cursor that is blowing things up.

reference: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-nocount-transact-sql?view=sql-server-ver15

score 1 · Answer 7 · answered Mar 07 '21 at 07:03

1

In my case the problem was that there were 2 (TWO) stored procedures with the UPDATE. SO, try to make just ONE stored procedure. Thank you!

answered Mar 07 '21 at 07:03

MikeRyz

209
1
3
18

score 0 · Answer 8 · answered May 08 '14 at 06:26

0

ALTER your database for RECURSIVE_TRIGGERS.

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.

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

Hope your problem will resolve.

answered May 08 '14 at 06:26

Pradeep atkari

549
1
8
14

That's not a very good solution. First this is a huge change in a prod environment. Secondly, you are avoiding a problem, not finding the root issue and fixing it. – deutschZuid Aug 16 '20 at 22:17

Weihui Guo · Answer 9 · 2020-05-28T21:27:09.857

My problem is with a stored procedure, not a trigger, and no cursor. An EXEC command within the procedure is the reason as other answers pointed out. But I can't add GO anywhere before the EXEC command with SSMS v18.5. It gives me an error. Turns out the procedure nested inside has its own TRY/CATCH block. Adding GO at the end of that procedure doesn't solve the problem. I had to remove the TRY/CATCH block there to make it works. Not sure this is just me or it can be helpful to others.

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

9 Answers9

Linked