Writing HTML out from a stored procedure and emailing it
Solution 1
All of the usual stuff about that being a bad practice aside, what your ex-coworker seems to be doing is leveraging SQL Server's native XML capabilities. You are not getting the "td" tags because they are not being assigned in your sub-query.
If you look at his queries, you will see the "td = ..." constructs. The reason they work is because the sub-query is being treated as XML (due to the FOR XML PATH
construct) and thus the "td = " clauses are being mapped to XML nodes.
Try adding that to your code and see if you get the proper table cells...
BEGIN
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<b>Shots Statistics for ' + convert(varchar, GETDATE()-1, 101) + ':</b>' +
N'<table border="1" width="400">' +
CAST (
(SELECT td = 'Practice:' + PracticeName as Status, 'Aprima ID:'+ AprimaSiteID,
td = 'Daily Count:' + CAST(Daily AS nvarchar(5)), 'Monthly Count:' + CAST(Monthly AS nvarchar(5))
FROM [CriticalKeyDatabase].[dbo].[ShotsManagement]
ORDER BY Status
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>';
exec msdb.dbo.sp_send_dbmail @profile_name='aProfileName', @recipients='[email protected]', @body=@tableHTML, @subject='Daily Shots', @importance='High', @body_format = 'HTML'
END
GO
Solution 2
This will htmlify and email the contents of a #temp table (including column names). All of the styling is done with CSS, so just change @style if you want it to look different. If you want to include multiple tables, you can modify it to add @table2, @table3, etc.
Example use:
SELECT *
INTO #email_data
FROM <yadda yadda>
EXEC [dbo].[email_table]
@tablename = '#email_data'
,@recipients = '[email protected]; [email protected];'
,@subject = 'TPS Reports'
Scripts
CREATE PROCEDURE [dbo].[table_to_html] (
@tablename sysname,
@html xml OUTPUT,
@order varchar(4) = 'ASC'
) AS
BEGIN
DECLARE
@sql nvarchar(max),
@cols nvarchar(max),
@htmlcols xml,
@htmldata xml,
@object_id int = OBJECT_ID('[tempdb].[dbo].'+QUOTENAME(@tablename));
IF @order <> 'DESC' SET @order = 'ASC';
SELECT @cols = COALESCE(@cols+',','')+QUOTENAME([name])+' '+@order
FROM tempdb.sys.columns
WHERE object_id = @object_id
ORDER BY [column_id];
SET @htmlcols = (
SELECT [name] AS [th]
FROM tempdb.sys.columns
WHERE object_id = @object_id
ORDER BY [column_id] FOR XML PATH(''),ROOT('tr')
);
SELECT @sql = COALESCE(@sql+',','SELECT @htmldata = (SELECT ')+'ISNULL(LTRIM('+QUOTENAME([name])+'),''NULL'') AS [td]'
FROM tempdb.sys.columns
WHERE object_id = @object_id
ORDER BY [column_id];
SET @sql = @sql + ' FROM '+QUOTENAME(@tablename)+' ORDER BY '+@cols+' FOR XML RAW(''tr''), ELEMENTS)';
EXEC sp_executesql @sql, N'@htmldata xml OUTPUT', @htmldata OUTPUT
SET @html = (SELECT @htmlcols,@htmldata FOR XML PATH('table'));
END
GO
CREATE PROCEDURE [dbo].[email_table] (
@tablename sysname,
@recipients nvarchar(max),
@subject nvarchar(max) = '',
@order varchar(4) = 'ASC'
) AS
BEGIN
IF OBJECT_ID('[tempdb].[dbo].'+QUOTENAME(@tablename)) IS NULL RAISERROR('Table does not exist. [dbo].[email_table] only works with temporary tables.',16,1);
DECLARE @style varchar(max) = 'table {border-collapse:collapse;} td,th {white-space:nowrap;border:solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;} th {border-bottom-width:2px;}';
DECLARE @table1 xml;
EXEC [dbo].[table_to_html] @tablename, @table1 OUTPUT, @order;
DECLARE @email_body AS nvarchar(max) = (
SELECT
(SELECT
@style AS [style]
FOR XML PATH('head'),TYPE),
(SELECT
@table1
FOR XML PATH('body'),TYPE)
FOR XML PATH('html')
);
EXEC msdb.dbo.sp_send_dbmail
@recipients = @recipients,
@subject = @subject,
@body = @email_body,
@body_format = 'html';
END
GO
Adrian
Updated on June 05, 2022Comments
-
Adrian about 2 years
My boss insists I send him a daily status report "the hacker way" by outputing HTML from a stored procedure and sending the return to his email using Database mailer. My colleague and I both agree that we should be using SSRS for this matter but since we aren't the ones with the money we unfortunately have to get stuck doing it this way. I myself have never seen this done and am having some formatting issues getting my table to format with cells. Can anyone shed some light on how to get this to work?
BEGIN DECLARE @tableHTML NVARCHAR(MAX) ; SET @tableHTML = N'<b>Shots Statistics for ' + convert(varchar, GETDATE()-1, 101) + ':</b>' + N'<table border="1" width="400">' + CAST ( (SELECT 'Practice:' + PracticeName as Status, 'Aprima ID:'+ AprimaSiteID, 'Daily Count:' + CAST(Daily AS nvarchar(5)), 'Monthly Count:' + CAST(Monthly AS nvarchar(5)) FROM [CriticalKeyDatabase].[dbo].[ShotsManagement] ORDER BY Status FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>'; exec msdb.dbo.sp_send_dbmail @profile_name='aProfileName', @recipients='[email protected]', @body=@tableHTML, @subject='Daily Shots', @importance='High', @body_format = 'HTML' END GO
I am basing this off a previous employee he had who gave him the idea. I don't see any declarations for cells anywhere in his query. I am not the best at SQL either..
DECLARE @tableHTML NVARCHAR(MAX) ; SET @tableHTML = N'<b>Message Statistics for ' + convert(varchar, GETDATE()-1, 101) + ':</b>' + N'<table border="1" width="400">' + CAST ( ( SELECT td = Status, ' ', td = convert(varchar, StatusValue) + '', ' ' FROM (SELECT 'Unsent Messages' as Status, Count(*) as StatusValue FROM [CriticalKeyDatabase].[dbo].[AllJobs_V] where convert(varchar, CreateDate, 101) = convert(varchar, GETDATE()-1, 101) and ReadyToSend = 1 UNION SELECT 'Total Messages' as Status, Count(*) as StatusValue FROM [CriticalKeyDatabase].[dbo].[AllJobs_V] where convert(varchar, CreateDate, 101) = convert(varchar, GETDATE()-1, 101)) A ORDER BY StatusValue FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' + N'<b>Sender Statistics for ' + convert(varchar, GETDATE()-1, 101) + ':</b>' + N'<table border="1" width="400">' + CAST ( ( SELECT td = Status, ' ', td = convert(varchar, StatusValue) + '', ' ' FROM (SELECT 'Sender: ' + Requestor as Status, Count(*) as StatusValue FROM [CriticalKeyDatabase].[dbo].[AllJobs_V] where convert(varchar, CreateDate, 101) = convert(varchar, GETDATE()-1, 101) group by Requestor) A ORDER BY Status FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' + N'<b>Recipient Statistics for ' + convert(varchar, GETDATE()-1, 101) + ':</b>' + N'<table border="1" width="400">' + CAST ( ( SELECT td = Status, ' ', td = convert(varchar, StatusValue) + '', ' ' FROM (SELECT 'Recipient: ' + Recipient as Status, Count(*) as StatusValue FROM [CriticalKeyDatabase].[dbo].[AllJobs_V] where convert(varchar, CreateDate, 101) = convert(varchar, GETDATE()-1, 101) group by Recipient) A ORDER BY Status FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' + N'<b>Event Statistics for ' + convert(varchar, GETDATE()-1, 101) + ':</b>' + N'<table border="1" width="400">' + CAST ( ( SELECT td = Status, ' ', td = convert(varchar, StatusValue) + '', ' ' FROM (SELECT 'Event: ' + EventType as Status, Count(*) as StatusValue FROM [CriticalKeyDatabase].[dbo].[AllJobs_V] where convert(varchar, CreateDate, 101) = convert(varchar, GETDATE()-1, 101) group by EventType) A ORDER BY Status FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>'; exec msdb.dbo.sp_send_dbmail @profile_name='Localhost', @recipients='[email protected]', @body=@tableHTML, @subject='Daily Statistics', @importance='High', @body_format = 'HTML' END
The previous employee's reports looked like this.
But mine are coming out like this.
-
Adrian almost 10 yearsThank you nice person :)
-
Adrian almost 10 yearsThis level of programming in sql is above me. If you were to give a few sentences in laymen's terms on how to use it what would it be? I am assuming all I have to do is run these queries to create the procedures then create a new query to create a temp table with the data I need and pass it into these two queries?
-
Anon almost 10 years@Adrian
table_to_html
looks up the names of the table's columns. It uses that to generate a table header and to build a query that selects all the data from the table. Then it uses XML formatting to add the basic html table tags.email_table
pairs that html with some CSS to make it pretty and sends the email.