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

本文介绍了超出最大存储过程、函数、触发器或视图嵌套级别(限制 32)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着跟版网的小编来一起学习吧！

问题描述

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

消息 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).

`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`

这篇关于超出最大存储过程、函数、触发器或视图嵌套级别(限制 32)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！

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

问题描述

相关文档推荐