sql-server – 超出最大存储过程,功能,触发或视图嵌套级别(限制32)

发布时间：2020-05-23 12:25:22 所属栏目：MsSql 来源：互联网

导读：我正在创建一个存储过程,但是在执行该过程时,我得到了特定的错误. 消息217,级别16,状态1,过程SendMail_Renewapp,第77行超过最大存储过程,功能,触发或视图嵌套级别(限制32). 任何人都可以帮助我解决这个问题. 我的程序如下 `ALTER PROCEDURE [dbo].[SendMail_

我正在创建一个存储过程,但是在执行该过程时,我得到了特定的错误.

消息217,级别16,状态1,过程SendMail_Renewapp,第77行
超过最大存储过程,功能,触发或视图嵌套级别(限制32).

任何人都可以帮助我解决这个问题.

我的程序如下

`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,@emailid
                END
                    CLOSE AppCursor
                    DEALLOCATE AppCursor
                --Fetching the application details: end--


  END`

解决方法

使用 “走” 之后结束声明

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!