问题描述
我正在创建一个存储过程，但在执行该过程时出现特定错误.
消息 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
这篇关于超出最大存储过程、函数、触发器或视图嵌套级别(限制 32)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！