Reputation: 451

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

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='[email protected]', 
@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`

Upvotes: 45

Answers (10)

WAChattha05

Reputation: 1

In my case return worked. I was getting infinite outputs through my stored procedure with nested error as above mentioned in query. RETURN would work with this

Upvotes: 0

MikeRyz

Reputation: 289

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!

Upvotes: 1

Selvin Medina

Reputation: 51

Use

RETURN

at the end of the procedure

Upvotes: 5

Weihui Guo

Reputation: 3997

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.

Upvotes: 0

Jim

Reputation: 962

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

Upvotes: 2

M Daim Khan

Reputation: 359

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.

Upvotes: 14

jmag

Reputation: 826

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.

Upvotes: 34

user2936035

Reputation: 555

Use the "Go" after the END statement

Upvotes: 53

Pradeep atkari

Reputation: 569

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.

Upvotes: 0

Squirrel5853

Reputation: 2406

Remove the BEGIN and END for your IF statement

example

WHILE @@FETCH_STATUS = 0
BEGIN

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

Upvotes: 6

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

Answers (10)

Related Questions