locked
Email using sql_db_email RRS feed

  • 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='xyz@gmail.com',
     
    		@subject = @MailSubject,
    		@body = @tableHTML,
    		@body_format = 'HTML' ;
    		FETCH NEXT FROM cursor_Location INTO @Location
    	END
    	CLOSE cursor_Location;
    	DEALLOCATE cursor_Location;
    end
    

    Thanks

    Tuesday, July 9, 2019 5:13 AM

All replies