В относительно недавнем прошлом, возникла задача автоматизировать процесс генерации и рассылки HTML отчетов руководству по продажам за текущий месяц. Так уж вышло, что для каждого руководящего лица создавались отдельные таблицы с необходимой только им информацией.
Поскольку, для каждого отчета, все делалось вручную, для начала мы решили воспользоваться возможностями dbForge, который позволял делать экспорт группы таблиц в HTML формат.
Однако, рассылку по-прежнему приходилось формировать вручную, что, мягко говоря, было нерациональным.
Было решено генерировать HTML со стороны сервера базы данных и через Database Mail формировать рассылку путем выполнения команды sp_send_dbmail .
Большинство примеров, приведенный в Сети, создавали разметку вручную - это было не слишком эффективным подходом. При этом я не нашел универсального решения, позволяющего работать с таблицой имеющей произвольную структуру.
Чтобы заполнить этот пробел предлагаю на рассмотрение мой вариант решения.
Из системного представления получаем список столбцов для требуемой таблицы:
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
Далее динамическим SQL создаем запрос, который генерирует 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
При этом столбцы, содержащие специфичные типы данных (например, UNIQUEIDENTIFIER ) в генерируемый отчет не включаются:
SELECT name FROM sys.types WHERE user_type_id IN ( 34, 36, 98, 128, 129, 130, 165, 173, 189, 241 )
При выполнении запроса мы получаем следующую HTML разметку, которая прикреплялась к письму:
<html> <header> <style type="text/css"> ... </style> </header> <body> <table> <tr> <th>column_name1</th> <th>column_name2</th> ... </tr> <tr> <td>column_value1</td> <td>column_value2</td> ... </tr> </table> </body> </html>
Чтобы вручную не выполнять этот скрипт каждую неделю, в SQL Agent был добавлен Job, который автоматически генерировал и отправлял отчеты.
Надеюсь, что приведенное здесь решение будет полезно при решении подобных задач.
PS: Многострочная конструкция VALUES появилась только в SQL Server 2008, поэтому, для экономии времени, привожу пример того же скрипта, но для 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