NAGARAJA H I
NAGARAJA H I

Reputation: 149

Send email in a tabular format using SQL Server database mail

enter image description here

I have created SSIS package which has 1 SQL task (stored procedure which returns list of below 4 columns. How will achieve the following table to be sent through email. Immediate Next task here what I would do is create another stored procdure which accepts the result set from 1st stored proc through another Sql task and 2nd stored proc contains msdb_SendDBemail call . Is there any better approach

Upvotes: 4

Views: 5708

Answers (2)

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try something like this to get data in tabular format. To execute row by row you need to implement a cursor like this.

CREATE PROCEDURE [dbo].[SendNewDeviceInfoToEmployee]  

AS  
BEGIN  
SET NOCOUNT ON

DECLARE @Mbody VARCHAR(MAX), 
@Sub VARCHAR(500), 
@EmpEmailPwdId BIGINT, 
@Emp_nm VARCHAR(150), 
@ToEmailid VARCHAR(150), 
@NewEmailId VARCHAR(150), 
DECLARE @Recipients VARCHAR(max)

DECLARE SendautoLoginDtl CURSOR FOR
SELECT EmpEmailPwdId, EmpName, ToEmailid, NewEmailId, EmpPassword 
FROM EmployeeEmailIdPassword WHERE ISNULL(Freeze,'N') = 'N' AND ISNULL(IsMailSent,'N') = 'N' AND ISNULL(ToEmailId,'') <> '' AND ISNULL(NewEmailId,'') <> ''
ORDER BY EmpEmailPwdId

OPEN SendautoLoginDtl;
FETCH NEXT FROM SendautoLoginDtl INTO @EmpEmailPwdId, @emp_nm, @ToEmailid, @NewEmailId
WHILE( @@FETCH_STATUS = 0 )
BEGIN

SET @xml = CAST(( SELECT [Rank] AS 'td','',[Player Name] AS 'td','', [Ranking Points] AS 'td','', Country AS 'td'
FROM #Temp 
ORDER BY Rank 
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @Mbody =N'<html><body><H3>Tennis Rankings Info</H3>
        <table border = 1> 
        <tr>
        <th> Rank </th> <th> Player Name </th> <th> Ranking Points </th> <th> Country </th></tr>'    

        SET @body = @body + @xml +'</table></body></html>' 

SET @sub='Your New Device Details - ' + @emp_nm  

set @Recipients =  @ToEmailid 

EXEC msdb.dbo.sp_send_dbmail '<YourDBMailProfileName>' , 
@recipients = @Recipients ,
@copy_recipients = @NewEmailId,
@subject = @sub ,
@body = @Mbody ,  
@body_format = 'HTML'  

--Update table if required
UPDATE <YourTable>
SET IsMailSent = 'Y'
WHERE EmpEmailPwdId = @EmpEmailPwdId

FETCH NEXT FROM SendautoLoginDtl INTO @EmpEmailPwdId, @emp_nm, @ToEmailid, @NewEmailId, @EmpPassword
END

CLOSE SendautoLoginDtl;
DEALLOCATE SendautoLoginDtl;

END

You can learn about cursor here.

Upvotes: 2

Alexander Volok
Alexander Volok

Reputation: 5940

Nice thread on stack exchange: Need to Send a formatted HTML Email via Database Mail in Sql Server 2008 R2

It has an answer with a code of stored procedure dbo.HtmlTable that can generate HTML for further use in database mail:

CREATE table ##foo (bar1 int, bar2 varchar(20), bar3 datetime)
INSERT into ##foo values (1, 'Abcdef', getdate())
INSERT into ##foo values (2, 'Ghijkl', '05/05/15')

DECLARE @tableHtml varchar(max)
EXEC dbo.HtmlTable
    '##foo',
    @tableHtml output
PRINT @tableHtml   

@tableHtml will contain html that renders into:

https://i.sstatic.net/jc014.jpg

Upvotes: 2

Related Questions