Maximum stored procedure, function, trigger, or vi

2019-01-25 04:27发布

站内文章 / SQL-Server
22 0 0

问题:

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`

回答1:

Use the "Go" after the END statement



回答2:

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.



回答3:

Remove the BEGIN and END for your IF statement

example

WHILE @@FETCH_STATUS = 0
BEGIN

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


回答4:

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.



回答5:

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.



标签： sql-server tsql stored-procedures
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~