В относительно недавнем прошлом, возникла задача автоматизировать процесс генерации и рассылки 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
Автор: AlanDenton
