T-SQL; How to add sentences before and after a table in an html format using sp_send_dbmail

Question

The MS Library site shows how to create an email and embed an html table result. https://msdn.microsoft.com/en-us/library/ms190307.aspx

This is fine, but how should you add the beginning and end wording to the email body table please? (it must be obvious, surely?)

If the initial code is as per below, then the optimum output would be: -

Hi,(carriage return)
Here is a list.(carriage return)
The details are below:(carriage return)

(then table goes here)

(carriage return)
Thank you for looking.(carriage return)
Kind regards(carriage return)
(carriage return)
From us

Table example code:

DECLARE @tableHTML  NVARCHAR(MAX) ;  

SET @tableHTML =  
N'<H1>Work Order Report</H1>' +  
N'<table border="1">' +  
N'<tr><th>Work Order ID</th><th>Product ID</th>' +  
N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +  
N'<th>Expected Revenue</th></tr>' +  
CAST ( ( SELECT td = wo.WorkOrderID,       '',  
                td = p.ProductID, '',  
                td = p.Name, '',  
                td = wo.OrderQty, '',  
                td = wo.DueDate, '',  
                td = (p.ListPrice - p.StandardCost) * wo.OrderQty  
          FROM AdventureWorks.Production.WorkOrder as wo  
          JOIN AdventureWorks.Production.Product AS p  
          ON wo.ProductID = p.ProductID  
          WHERE DueDate > '2004-04-30'  
            AND DATEDIFF(dd, '2004-04-30', DueDate) < 2   
          ORDER BY DueDate ASC,  
                   (p.ListPrice - p.StandardCost) * wo.OrderQty DESC  
          FOR XML PATH('tr'), TYPE   
) AS NVARCHAR(MAX) ) +  
N'</table>' ;  

EXEC msdb.dbo.sp_send_dbmail @recipients='yourfriend@Adventure-Works.com',  
@subject = 'Work Order List',  
@body = @tableHTML,  
@body_format = 'HTML' ; 

Answer 1

Use this instead...

DECLARE @header NVARCHAR(MAX)
DECLARE @footer NVARCHAR(MAX)
DECLARE @tableHTML  NVARCHAR(MAX) ;  

SET @header = 'Hi, </br> Here is a list. </br> The details are below </br>'
SET @footer = '</br> Thank you for looking. </br> Kind regards </br> From us'

SET @tableHTML = @header +  
N'<H1>Work Order Report</H1>' +  
N'<table border="1">' +  
N'<tr><th>Work Order ID</th><th>Product ID</th>' +  
N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +  
N'<th>Expected Revenue</th></tr>' +  
CAST ( ( SELECT td = wo.WorkOrderID,       '',  
                td = p.ProductID, '',  
                td = p.Name, '',  
                td = wo.OrderQty, '',  
                td = wo.DueDate, '',  
                td = (p.ListPrice - p.StandardCost) * wo.OrderQty  
          FROM AdventureWorks.Production.WorkOrder as wo  
          JOIN AdventureWorks.Production.Product AS p  
          ON wo.ProductID = p.ProductID  
          WHERE DueDate > '2004-04-30'  
            AND DATEDIFF(dd, '2004-04-30', DueDate) < 2   
          ORDER BY DueDate ASC,  
                   (p.ListPrice - p.StandardCost) * wo.OrderQty DESC  
          FOR XML PATH('tr'), TYPE   
) AS NVARCHAR(MAX) ) + 
N'</table>' + @footer 


    EXEC msdb.dbo.sp_send_dbmail @recipients='yourfriend@Adventure-Works.com',  
    @subject = 'Work Order List',  
    @body = @tableHTML,  
    @body_format = 'HTML' ; 

This is my output