CODEWITHSUNDEEP
sqlsql-server
Joyиal Ali
Joyиal Ali

Reputation: 89

Multiple tables in SQL Server email

The code below allows me to send emails with one table.

I am unsure how to create another table in the email and use another query so that I can display both results in one email

FYI I'm using SQL Server 2012

SELECT * 
FROM #WKPLAN

DECLARE @xml NVARCHAR(MAX)
DECLARE @body1 NVARCHAR(MAX)
DECLARE @EmailBody AS VARCHAR(MAX)

SET @xml = CAST((SELECT
                     [Day of Week] AS 'td', '',
                     [Cycle_%_MTD] as 'td', ''
                 FROM 
                     #WKPLAN
                 FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX))

SET @body1 ='<html><body><H5>See below for the weekend plan</H5>
<table border = 1> 
<tr><th><center>Day of Week</center></th></td>' +
N'<th><center>Cycle % MTD</center></th></td>'

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

EXEC data.dbo.usp_EmailProgress
          @Reply_to = ‘’,
          @Recipients = ‘’,
          @copy_recipients = ‘’,
          @Subject = 'Email,
          @body = @body1,
          @body_format ='HTML'

Upvotes: 0

Views: 1607

Answers (2)

Joyиal Ali
Joyиal Ali

Reputation: 89

DECLARE @xml NVARCHAR(MAX)
DECLARE @body1 NVARCHAR(MAX)
DECLARE @EmailBody AS VARCHAR(MAX)
SET @xml =CAST(( 

Select --[Date] as 'td', '',
       [Day of Week] as 'td', '',
         [Cycle_%_MTD] as 'td', ''


FROM #WKPLAN

FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX))

DECLARE @xml2 NVARCHAR(MAX)
DECLARE @body2 NVARCHAR(MAX)
DECLARE @EmailBody2 AS VARCHAR(MAX)
SET @xml2 = cast(( 

Select     [Day of Week] as 'td', '',
         [Cycle_%_MTD] as 'td', ''

         FROM #WKPLAN
FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX))


SET @body1 ='<html><body><H5>See below for the weekend plan</H5>
<table border = 1> 
<tr><th><center>Day of Week</center></th></td>' +
N'<th><center>Cycle % MTD</center></th></td>'

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

SET @body2 ='<html><body><H5>Test</H5>
<table border = 1> 

<tr><th><center>Day of Week</center></th></td>' +
N'<th><center>Cycle % MTD</center></th></td>'

SET @body1 = @body1 + '<H5>' + @xml2 + '</H5>' + '</table></body></html>'

EXEC data.dbo.usp_EmailProgress
 @Reply_to = ‘’
,@Recipients = ‘’
,@copy_recipients = ''
,@body = @body1
,@body_format ='HTML'
,@subject = 'Test'

Its working fine but the second query isn't showing in a table just in text all togather :(

Upvotes: 0

user2366842
user2366842

Reputation: 1216

SET @body1 = @body1 + '<H5>' + @xml + '</H5>' + '</table> then create a new line as such SET @body1 = @body1 + '<table>' + (whatever) and one more line to close out the body and html tags SET @body1 = @body1 + '</table></body></html>' You'll also need to declare another xml variable and store your second query results to it... Your new code would look like so:

SELECT * 
FROM #WKPLAN

DECLARE @xml NVARCHAR(MAX)
DECLARE @body1 NVARCHAR(MAX)
DECLARE @EmailBody AS VARCHAR(MAX)

SET @xml = CAST((SELECT
                     [Day of Week] AS 'td', '',
                     [Cycle_%_MTD] as 'td', ''
                 FROM 
                     #WKPLAN
                 FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX))
DECLARE @xml2 NVARCHAR(MAX)
 SET @xml2 = (whatever your second query is)

SET @body1 ='<html><body><H5>See below for the weekend plan</H5>
<table border = 1> 
<tr><th><center>Day of Week</center></th></td>' +
N'<th><center>Cycle % MTD</center></th></td>'

SET @body1 = @body1 + '<H5>' + @xml + '</H5>' + '</table>'
SET @body1 = @body1 + '<H5>(whatever you want the title of the second table to be)</H5><table border = 1><tr><th><center>Column1</center></th></td><th><center>Column2</center></th></td>' + @xml2
SET @body1 = @body1 + '</body></html>'


EXEC data.dbo.usp_EmailProgress
          @Reply_to = ‘’,
          @Recipients = ‘’,
          @copy_recipients = ‘’,
          @Subject = 'Email',
          @body = @body1,
          @body_format ='HTML'

Upvotes: 1

Related Questions