Send T-SQL query results in a HTML table
If you need to email out t-sql query results in a HTML table, whether on an ad-hoc basis or regularly via a job, it can be useful to present the results in a HTML formatted table. This makes the output much easier to read for the recipients, especially if they are not used to using management studio and seeing unformatted data.
This simple script shows you how to do this. I have used the Adventureworks2012 database for the example, all that needs to be done to make it relevant to your real data is to update the query part and table headers as well as other text fields. You can also change the css if you know what you are doing to use different colours, fonts and other styles.
/***************************************************************************************
* Send email script
****************************************************************************************/
DECLARE @EmailRecipient VARCHAR(100), @SubjectText VARCHAR(100), @ProfileName VARCHAR(100), @Body VARCHAR(MAX)
SELECT TOP 1 @EmailRecipient = 'testemail@gmail.com'
SELECT @SubjectText = 'Top 10 Sales YTD'
SELECT TOP 1 @ProfileName = name FROM msdb.dbo.sysmail_profile
SET @Body =
N'<H4 style="color:#000080; font-family:Arial,Verdana">AdventureWorks2012</H4>' +
N'<H5 style="color:#000080; font-family:Arial,Verdana">Top 10 Sales Year to date</H5>' +
N'<table border="3"; style="font-family:Arial,Verdana; text-align:left; font-size:9pt; color:#000033">' +
N'<tr style="text-align:left;">
<th style="text-align:left;background-color:#000080;color:#FFF;font-weight:bold; width:50%;">BusinessEntityID</th>
<th style="text-align:left;background-color:#000080;color:#FFF;font-weight:bold;width:25%;">SalesYTD</th>
<th style="text-align:left;background-color:#000080;color:#FFF;font-weight:bold;width:25%;">SalesLastYear</th>'
+
CAST ( ( SELECT TOP 10
td = [BusinessEntityID] , '',
td = [SalesYTD] , '',
td = [SalesLastYear]
FROM [AdventureWorks2012].[Sales].[SalesPerson] sp
ORDER BY SalesYTD DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName,
@recipients = @EmailRecipient,
@body_format = 'HTML',
@body = @Body,
@subject = @SubjectText;
/**************************************************************************************/
If you are going to be scheduling a job to send the email regularly, you might want to wrap some logic around it in case there is no data to report. This is particularly useful for error reporting for DBAs where you want to be alerted of errors but only when there are any, you don’t want a regular blank email.
/*************************************************************************************** * Adding logic script ****************************************************************************************/ IF EXISTS (SELECT 1 FROM [AdventureWorks2012].[Sales].[SalesPerson]) BEGIN -- Put your code in here END /***************************************************************************************/
Leave a Reply