Menu
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='[email protected]', @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` 
567
SET NOCOUNT ON prevents the sending of DONEINPROC messages to the client for each statement in a stored procedure.
A stored procedure is a set of Structured Query Language (SQL) statements with an assigned name, which are stored in a relational database management system (RDBMS) as a group, so it can be reused and shared by multiple programs.
Use the "Go" after the END statement
86
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.
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
