超出最大存储过程、函数、触发器或视图嵌套级别(限制 32)

2021-12-20 00:00:00 tsql sql-server stored-procedures

我正在创建一个存储过程，但在执行该过程时出现特定错误.

消息 217，级别 16，状态 1，过程 SendMail_Renewapp，第 77 行超出了最大存储过程、函数、触发器或视图嵌套级别(限制为 32).

谁能帮我解决这个问题.

我的程序如下..

`ALTER PROCEDURE [dbo].[SendMail_Renewapp]-- 这里添加存储过程的参数作为开始声明@xml nvarchar(max)声明@body nvarchar(max)声明@currentdate 日期时间；声明@ExpDate 日期时间；声明@mailsendingdate 日期时间；声明@renewtime varchar(10);声明@AgencyId int;声明@ApplicationID int;声明@emailid varchar(100);设置@currentdate=getdate();--获取应用详情:开始--DECLARE AppCursor CURSOR FOR在applications.ap_agency_id=agency.ag_id上从申请加入代理中选择前5个applications.ap_id,applications.ap_expiry_date,agency.ag_co_email其中 ap_status='AS' 和 ap_iame_flag='IA' 和 ap_expiry_date != ''打开应用程序光标从 AppCursor 中提取下一个 @ApplicationID,@ExpDate,@emailid而@@FETCH_STATUS = 0开始SET @renewtime = ABS(DATEDIFF(day, @currentdate, @ExpDate))如果(@renewtime=180)开始--SET @xml = CAST(( SELECT [ag_id] AS 'td','',[ag_name] AS 'td','',[ag_co_email] AS 'td','',[ag_mobile] AS 'td'来自 beesl.dbo.Agency 其中 @renewtime <180--FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))SET @body='<身体><div><div><H3>过去 3 个月未付款的机构详情</H3><table cellpadding="4" cellspacing="1" bgcolor=#FFFFFF border=1 rules=none frame=box ><tr ><th style=border:1px solid #000000;align=left bgcolor=#c2c2c2>代理 ID </th><th style=border:1px solid #000000;align=left bgcolor=#c2c2c2>机构名称 </th><th style=border:1px solid #000000;align=left bgcolor=#c2c2c2>代理电子邮件</th><th style=border:1px solid #000000;align=left bgcolor=#c2c2c2>联系电话</tr>'SET @body = @body + @xml +'</table></div></body></html>'执行 msdb.dbo.sp_send_dbmail@profile_name='蜜蜂'，@recipients='emailid@emailid.com',@subject='更新应用程序'，--@file_attachments = 'D:eelogo.png',@重要性=高，--@body = '测试'@body = @body，@body_format ='HTML';结尾从 AppCursor 中提取下一个 @ApplicationID,@ExpDate,@emailid结尾关闭应用程序光标解除分配应用程序光标--获取应用详情:结束--结束`

解决方案

使用去"之后结尾声明

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:eelogo.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`

解决方案

Use the "Go" after the END statement

相关文章