Introduction
This tip describes the most convenient way of process automation of generating HTML reports for dynamic table-structures and sending them to different people.
Background
In
the relatively recent past, there was a task - to automate the
process of generating HTML reports with current month's sales results
and sending them to all the managers. It just so happened that
for each manager a table was created with specific information
required for them.
Since
every action was performed manually for each report, we decided to
take advantage of dbForge Studio capabilities, that allowed to export
groups of tables in the HTML format.
However,
mail delivery still had to be generated manually, that, to put it
mildly, was irrational.
Using the Code
It
was decided to generate HTML on the server side and build the mail
delivery list via Database Mail by executing the sp_send_dbmail
command.
Most
examples on the Web were about manual HTML markup creating — it was
less than an efficient approach. Thus, I haven't found a unified
solution, that would allow to work with dynamic table-structures.
To
fill in this gap, here is my solution.
DECLARE @object_name SYSNAME
, @object_id INT
, @SQL NVARCHAR(MAX)
SELECT @object_name = '[dbo].[Products]'
, @object_id = OBJECT_ID(@object_name)
SELECT @SQL = 'SELECT
[header/style/@type] = ''text/css''
, [header/style] = ''
table {border-collapse:collapse;}
td, table {
border:1px solid silver;
padding:3px;
}
th, td {
vertical-align: top;
font-family: Tahoma;
font-size: 8pt;
text-align: left;
}''
, body = (
SELECT * FROM (
SELECT tr = (
SELECT * FROM (
VALUES ' +
STUFF(CAST((
SELECT ', (''' + c.name + ''')'
FROM sys.columns c WITH(NOLOCK)
WHERE c.[object_id] = @object_id
AND c.system_type_id NOT IN (34, 36, 98, 128, 129, 130, 165, 173, 189,
241)
ORDER BY c.column_id
FOR XML PATH(''), TYPE) AS NVARCHAR(MAX)), 1, 2, '') + '
) t (th)
FOR XML PATH('''')
)
UNION ALL
SELECT (
SELECT * FROM (
VALUES' + STUFF(CAST((
SELECT ', ' +
CASE WHEN c.is_nullable = 1
THEN '(ISNULL(' ELSE '(' END +
CASE WHEN TYPE_NAME(c.system_type_id) _
NOT IN ('nvarchar', 'nchar', 'varchar', 'char')
THEN 'CAST(' + '[' + c.name + '] AS NVARCHAR(MAX))' ELSE '[' + c.name
+ ']' END +
CASE WHEN c.is_nullable = 1
THEN ',''''))' ELSE ')' END
FROM sys.columns c WITH(NOLOCK)
WHERE c.[object_id] = @object_id
AND c.system_type_id NOT IN (34, 36, 98, 128, 129, 130, 165, 173, 189, 241)
ORDER BY c.column_id
FOR XML PATH(''), TYPE) AS NVARCHAR(MAX)), 1, 2, ' ') + '
) t (td)
FOR XML PATH(''''), TYPE)
FROM ' + @object_name + '
) t
FOR XML PATH(''''), ROOT(''table''), TYPE
)
FOR XML PATH(''''), ROOT(''html''), TYPE'
PRINT @SQL
EXEC sys.sp_executesql @SQL
Next, use dynamic SQL to create a query, that generates XML:
SELECT
[header/style/@type] = 'text/css'
, [header/style] = 'css style ...'
, body = (
SELECT *
FROM (
SELECT tr = (
SELECT *
FROM (
VALUES ('column_name1', 'column_name2', ...)
) t (th)
FOR XML PATH('')
)
UNION ALL
SELECT (
SELECT *
FROM (
VALUES ([column_value1], [column_value2], ...)
)t (td)
FOR XML PATH(''), TYPE
)
FROM [table]
) t
FOR XML PATH(''), ROOT('table'), TYPE
)
FOR XML PATH(''), ROOT('html'), TYPE
At
this, columns containing specific data types (e.g., UNIQUEIDENTIFIER)
are not included into the generated report:
SELECT name
FROM sys.types
WHERE user_type_id IN (
34, 36, 98,
128, 129, 130,
165, 173, 189, 241
)
On
query execution, we get the following HTML markup, that is attached to
the mail:
<html>
<header>
<style type="text/css">
...
</style>
</header>
<body>
...
<table>
<tbody><tr>
<th>column_name1</th>
<th>column_name2</th></tr>
<tr>
<td>column_value1</td>
<td>column_value2</td></tr></tbody></table>
</body>
</html>In
order not to execute this script manually every week, a Job was added
to SQL Agent, that automatically generated and sent reports.
I
hope the solution provided here will be useful to resolve similar
tasks.
P.S.: The VALUES multiline statement appeared only in SQL
Server 2008. So here is an example of the same script for SQL Server
2005:
DECLARE @object_name SYSNAME
, @object_id INT
, @SQL NVARCHAR(MAX)
SELECT @object_name = '[dbo].[Products]'
, @object_id = OBJECT_ID(@object_name)
SELECT @SQL = 'SELECT
[header/style/@type] = ''text/css''
, [header/style] = ''
table {border-collapse:collapse;}
td, table {
border:1px solid silver;
padding:3px;
}
th, td {
vertical-align: top;
font-family: Tahoma;
font-size: 8pt;
text-align: left;
}''
, body = (
SELECT * FROM (
SELECT tr = (
SELECT * FROM (
' +
STUFF(CAST((
SELECT ' UNION ALL SELECT ''' + c.name + ''''
FROM sys.columns c WITH(NOLOCK)
WHERE c.[object_id] = @object_id
AND c.system_type_id NOT IN (34, 36, 98, 128, 129, 130, 165, 173, 189, 241)
ORDER BY c.column_id
FOR XML PATH(''), TYPE) AS NVARCHAR(MAX)), 1, 17, 'SELECT th =') + '
) t
FOR XML PATH('''')
)
UNION ALL
SELECT (
SELECT * FROM (
' + STUFF(CAST((
SELECT ' UNION ALL SELECT ' +
CASE WHEN c.is_nullable = 1
THEN 'ISNULL(' ELSE '' END +
CASE WHEN TYPE_NAME(c.system_type_id) _
NOT IN ('nvarchar', 'nchar', 'varchar', 'char')
THEN 'CAST(' + '[' + c.name + '] _
AS NVARCHAR(MAX))' ELSE '[' + c.name + ']' END +
CASE WHEN c.is_nullable = 1
THEN ','''')' ELSE '' END
FROM sys.columns c WITH(NOLOCK)
WHERE c.[object_id] = @object_id
AND c.system_type_id NOT IN (34, 36, 98, 128, 129, 130, 165, 173, 189, 241)
ORDER BY c.column_id
FOR XML PATH(''), TYPE) AS NVARCHAR(MAX)), 1, 17, 'SELECT td =') + '
) t
FOR XML PATH(''''), TYPE)
FROM ' + @object_name + '
) t
FOR XML PATH(''''), ROOT(''table''), TYPE
)
FOR XML PATH(''''), ROOT(''html''), TYPE'
PRINT @SQL
EXEC sys.sp_executesql @SQL
