Email using sql_db_email

Question

User-797751191 posted

Hi

   Can we do below Procedure without using Cursors

ALTER proc [dbo].[sp_DAD_Mail_Test]
 
as
begin
	DECLARE @Location VARCHAR(15)
	DECLARE @tableHTML  NVARCHAR(MAX) ;
	DECLARE @BlankTableHTML  NVARCHAR(MAX) ;
	DECLARE @MailSubject  NVARCHAR(MAX) ;
	DECLARE @MailHeader  NVARCHAR(MAX) ;
	DECLARE @CCMail  NVARCHAR(MAX) ;
 
	
	DECLARE cursor_Location CURSOR FOR
	SELECT Distinct([Location]) FROM [Test1] 

	OPEN cursor_Location
	FETCH NEXT FROM cursor_Location INTO @Location

	Set @MailSubject='Data as on '+ CONVERT(VARCHAR(10),GETDATE(),103)+'';
	Set @MailHeader='<H4>Dear Sir/Madam ,<Br/><Br/> </H4>';
	
	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET @tableHTML =@MailHeader+
		N'<table border="1" style="font-size:12px;border: 1px solid #d4d4d4">' +
		N'<tr><th>SN.</th><th>Location</th><th>Document No</th><th>Date</th>' +
		N'<th>Customer Name</th>' +
 
		replace(REPLACE(CAST ( ( SELECT td = ROW_NUMBER() OVER(ORDER BY [Date]), '',
						td = [Location], '',
						td = [Document No_], '',
						td = CONVERT(VARCHAR(10),Date,103), '', 
						td = [Customer Name], ''
				  FROM [Test2]
				  where [Location] = @Location
				  order by [Date]
				  FOR XML PATH('tr'), TYPE 
		) AS VARCHAR(MAX) ),'&lt;','<'),'&gt;','>')  +
		N'</table><br/><br/>Thanks and Regards' ;

		EXEC msdb.dbo.sp_send_dbmail @recipients='[e-mail]',
 
		@subject = @MailSubject,
		@body = @tableHTML,
		@body_format = 'HTML' ;
		FETCH NEXT FROM cursor_Location INTO @Location
	END
	CLOSE cursor_Location;
	DEALLOCATE cursor_Location;
end

Thanks

Answer 1

User-1038772411 posted

Hi, Jsshivalik

You can refer below for send email using stored procedure.

https://www.emailarchitect.net/easendmail/kb/sql.aspx?cat=0

Thanks