将 SQL 查询结果表转换为用于电子邮件的 HTML 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7070053/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-29 10:05:35 来源:igfitidea点击:
Convert a SQL query result table to an HTML table for email
提问by Matt Victory
I am running a SQL query that returns a table of results. I want to send the table in an email using dbo.sp_send_dbMail.
我正在运行一个返回结果表的 SQL 查询。我想使用 dbo.sp_send_dbMail 在电子邮件中发送表格。
Is there a straightforward way within SQL to turn a table into an HTML table? Currently, I'm manually constructing it using COALESCE and putting the results into a varchar that I use as the emailBody.
在 SQL 中有一种直接的方法可以将表格转换为 HTML 表格吗？目前，我正在使用 COALESCE 手动构建它并将结果放入用作 emailBody 的 varchar。
Is there a better way to do this?
有一个更好的方法吗？
采纳答案by JustinStolle
Here is one way to do it from an article titled "Format query output into an HTML table - the easy way". You would need to substitute the details of your own query for the ones in this example, which gets a list of tables and a row count.
这是一篇题为“将查询输出格式设置为 HTML 表 - 简单方法”的文章中的一种方法。您需要将您自己的查询的详细信息替换为本示例中的查询，这将获取表列表和行数。
declare @body varchar(max)
set @body = cast( (
select td = dbtable + '</td><td>' + cast( entities as varchar(30) ) + '</td><td>' + cast( rows as varchar(30) )
from (
select dbtable = object_name( object_id ),
entities = count( distinct name ),
rows = count( * )
from sys.columns
group by object_name( object_id )
) as d
for xml path( 'tr' ), type ) as varchar(max) )
set @body = '<table cellpadding="2" cellspacing="2" border="1">'
+ '<tr><th>Database Table</th><th>Entity Count</th><th>Total Rows</th></tr>'
+ replace( replace( @body, '<', '<' ), '>', '>' )
+ '</table>'
print @body
Once you have @body, you can then use whatever email mechanism you want.
拥有 之后@body，您就可以使用任何您想要的电子邮件机制。
回答by MgSam
I made a dynamic proc which turns any random query into an HTML table, so you don't have to hardcode columns like in the other responses.
我制作了一个动态过程，它将任何随机查询转换为 HTML 表，因此您不必像在其他响应中那样对列进行硬编码。
-- Description: Turns a query into a formatted HTML table. Useful for emails.
-- Any ORDER BY clause needs to be passed in the separate ORDER BY parameter.
-- =============================================
CREATE PROC [dbo].[spQueryToHtmlTable]
(
@query nvarchar(MAX), --A query to turn into HTML format. It should not include an ORDER BY clause.
@orderBy nvarchar(MAX) = NULL, --An optional ORDER BY clause. It should contain the words 'ORDER BY'.
@html nvarchar(MAX) = NULL OUTPUT --The HTML output of the procedure.
)
AS
BEGIN
SET NOCOUNT ON;
IF @orderBy IS NULL BEGIN
SET @orderBy = ''
END
SET @orderBy = REPLACE(@orderBy, '''', '''''');
DECLARE @realQuery nvarchar(MAX) = '
DECLARE @headerRow nvarchar(MAX);
DECLARE @cols nvarchar(MAX);
SELECT * INTO #dynSql FROM (' + @query + ') sub;
SELECT @cols = COALESCE(@cols + '', '''''''', '', '''') + ''['' + name + ''] AS ''''td''''''
FROM tempdb.sys.columns
WHERE object_id = object_id(''tempdb..#dynSql'')
ORDER BY column_id;
SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''
EXEC sys.sp_executesql @cols, N''@html nvarchar(MAX) OUTPUT'', @html=@html OUTPUT
SELECT @headerRow = COALESCE(@headerRow + '''', '''') + ''<th>'' + name + ''</th>''
FROM tempdb.sys.columns
WHERE object_id = object_id(''tempdb..#dynSql'')
ORDER BY column_id;
SET @headerRow = ''<tr>'' + @headerRow + ''</tr>'';
SET @html = ''<table border="1">'' + @headerRow + @html + ''</table>'';
';
EXEC sys.sp_executesql @realQuery, N'@html nvarchar(MAX) OUTPUT', @html=@html OUTPUT
END
GO
Usage:
用法：
DECLARE @html nvarchar(MAX);
EXEC spQueryToHtmlTable @html = @html OUTPUT, @query = N'SELECT * FROM dbo.People', @orderBy = N'ORDER BY FirstName';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Foo',
@recipients = '[email protected];',
@subject = 'HTML email',
@body = @html,
@body_format = 'HTML',
@query_no_truncate = 1,
@attach_query_result_as_file = 0;
Related: Here is similar code to turn any arbitrary query into a CSV string.
相关：这是将任何任意查询转换为CSV 字符串的类似代码。
回答by Mahesh
This might give you some idea --
这可能会给你一些想法——
CREATE TABLE #Temp
(
[Rank] [int],
[Player Name] [varchar](128),
[Ranking Points] [int],
[Country] [varchar](128)
)
INSERT INTO #Temp
SELECT 1,'Rafael Nadal',12390,'Spain'
UNION ALL
SELECT 2,'Roger Federer',7965,'Switzerland'
UNION ALL
SELECT 3,'Novak Djokovic',7880,'Serbia'
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT [Rank] AS 'td','',[Player Name] AS 'td','',
[Ranking Points] AS 'td','', Country AS 'td'
FROM #Temp ORDER BY Rank
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Tennis Rankings Info</H3>
<table border = 1>
<tr>
<th> Rank </th> <th> Player Name </th> <th> Ranking Points </th> <th> Country </th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL ALERTING', -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = '[email protected]', -- replace with your email address
@subject = 'E-mail in Tabular Format' ;
DROP TABLE #Temp
回答by Emrah Saglam
Here my common used script below. I use this for running scripts on two tables/views with SQL job and send results as two HTML tables via mail. Ofcourse you should create mail profile before run this.
下面是我常用的脚本。我使用它在两个表/视图上运行脚本和 SQL 作业，并通过邮件将结果作为两个 HTML 表发送。当然，您应该在运行之前创建邮件配置文件。
DECLARE @mailfrom varchar(max)
DECLARE @subject varchar(100)
DECLARE @tableHTML NVARCHAR(MAX), @tableHTML1 NVARCHAR(MAX), @tableHTML2 NVARCHAR(MAX), @mailbody NVARCHAR(MAX)
DECLARE @Table1 NVARCHAR(MAX), @Table2 NVARCHAR(MAX)
DECLARE @jobName varchar(100)
SELECT @jobName = name from msdb..sysjobs where job_id = $(ESCAPE_NONE(JOBID))
-- If the result set is not empty then fill the Table1 HTML table
IF (SELECT COUNT(*) FROM [Database].[Schema].[Table1]) > 0
BEGIN
SET @Table1 = N''
SELECT @Table1 = @Table1 + '<tr style="font-size:13px;background-color:#FFFFFF">' +
'<td>' + ColumnText + '</td>' +
'<td>' + CAST(ColumnNumber as nvarchar(30)) + '</td>' + '</tr>'
FROM [Database].[Schema].[Table1]
ORDER BY ColumnText,ColumnNumber
SET @tableHTML1 =
N'<table border="1" align="Left" cellpadding="2" cellspacing="0" style="color:black;font-family:arial,helvetica,sans-serif;text-align:left;" >' +
N'<tr style ="font-size:13px;font-weight: normal;background: #FFFFFF">
<th align=left>ColumnTextHeader1</th>
<th align=left>ColumnNumberHeader2</th> </tr>' + @Table1 + '</table>'
END
ELSE
BEGIN
SET @tableHTML1 = N''
SET @Table1 = N''
END
-- If the result set is not empty then fill the Table2 HTML table
IF (SELECT COUNT(*) FROM [Database].[Schema].[Table2]) > 0
BEGIN
SET @Table2 = N''
SELECT @Table2 = @Table2 + '<tr style="font-size:13px;background-color:#FFFFFF">' +
'<td>' + ColumnText + '</td>' +
'<td>' + CAST(ColumnNumber as nvarchar(30)) + '</td>' + '</tr>'
FROM [Database].[Schema].[Table2]
ORDER BY ColumnText,ColumnNumber
SET @tableHTML2 =
N'<table border="1" align="Left" cellpadding="2" cellspacing="0" style="color:black;font-family:arial,helvetica,sans-serif;text-align:left;" >' +
N'<tr style ="font-size:13px;font-weight: normal;background: #FFFFFF">
<th align=left>ColumnTextHeader1</th>
<th align=left>ColumnNumberHeader2</th> </tr>' + @Table2 + '</table>'
END
ELSE
BEGIN
SET @tableHTML2 = N''
SET @Table2 = N''
END
SET @tableHTML = @tableHTML1 + @tableHTML2
-- If result sets from Table1 and Table2 are empty, then don't sent mail.
IF (SELECT @tableHTML) <> ''
BEGIN
SET @mailbody = N' Write mail text here<br><br>' + @tableHTML
SELECT @mailfrom = 'SQL Server <' + cast(SERVERPROPERTY('ComputerNamePhysicalNETBIOS') as varchar(50)) + '@domain.com>'
SELECT @subject = N'Mail Subject [Job: ' + @jobName + ']'
EXEC msdb.dbo.sp_send_dbmail
@profile_name= 'mailprofilename',
@recipients= '<[email protected]>',
@from_address = @mailfrom,
@reply_to = '<[email protected]>',
@subject = @subject,
@body = @mailbody,
@body_format = 'HTML'
-- ,@importance = 'HIGH'
END
回答by Suresh Gautam
Following piece of code, I have prepared for generating the HTML file for documentation which includes Table Name and Purpose in each table and Table Metadata information. It might be helpful!
下面的一段代码，我准备为文档生成 HTML 文件，其中包括每个表中的表名和用途以及表元数据信息。它可能会有所帮助！
use Your_Database_Name;
print '<!DOCTYPE html>'
PRINT '<html><body>'
SET NOCOUNT ON
DECLARE @tableName VARCHAR(30)
DECLARE tableCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT T.name AS TableName
FROM sys.objects AS T
WHERE T.type_desc = 'USER_TABLE'
ORDER BY T.name
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @tableName
WHILE @@FETCH_STATUS = 0 BEGIN
print '<table>'
print '<tr><td><b>Table Name: <b></td><td>'+@tableName+'</td></tr>'
print '<tr><td><b>Prupose: <b></td><td>????YOu can Fill later????</td></tr>'
print '</table>'
print '<table>'
print '<tr><th>ColumnName</th><th>DataType</th><th>Size</th><th>PrecScale</th><th>Nullable</th><th>Default</th><th>Identity</th><th>Remarks</th></tr>'
SELECT concat('<tr><td>',
LEFT(C.name, 30) /*AS ColumnName*/,'</td><td>',
LEFT(ISC.DATA_TYPE, 10) /*AS DataType*/,'</td><td>',
C.max_length /*AS Size*/,'</td><td>',
CAST(P.precision AS VARCHAR(4)) + '/' + CAST(P.scale AS VARCHAR(4)) /*AS PrecScale*/,'</td><td>',
CASE WHEN C.is_nullable = 1 THEN 'Null' ELSE 'No Null' END /*AS [Nullable]*/,'</td><td>',
LEFT(ISNULL(ISC.COLUMN_DEFAULT, ' '), 5) /*AS [Default]*/,'</td><td>',
CASE WHEN C.is_identity = 1 THEN 'Identity' ELSE '' END /*AS [Identity]*/,'</td><td></td></tr>')
FROM sys.objects AS T
JOIN sys.columns AS C ON T.object_id = C.object_id
JOIN sys.types AS P ON C.system_type_id = P.system_type_id and c.user_type_id = p.user_type_id
JOIN INFORMATION_SCHEMA.COLUMNS AS ISC ON T.name = ISC.TABLE_NAME AND C.name = ISC.COLUMN_NAME
WHERE T.type_desc = 'USER_TABLE'
AND T.name = @tableName
ORDER BY T.name, ISC.ORDINAL_POSITION
print '</table>'
print '</br>'
FETCH NEXT FROM tableCursor INTO @tableName
END
CLOSE tableCursor
DEALLOCATE tableCursor
SET NOCOUNT OFF
PRINT '</body></html>'
回答by BenRivet
based on JustinStolle code (thank you), I wanted a solution that could be generic without having to specify the column names.
基于 JustinStolle 代码（谢谢），我想要一个可以通用的解决方案，而无需指定列名。
This sample is using the data of a temp table but of course it can be adjusted as required.
本示例使用的是临时表的数据，但当然可以根据需要进行调整。
Here is what I got:
这是我得到的：
DECLARE @htmlTH VARCHAR(MAX) = '',
@htmlTD VARCHAR(MAX)
--get header, columns name
SELECT @htmlTH = @htmlTH + '<TH>' + name + '</TH>' FROM tempdb.sys.columns WHERE object_id = OBJECT_ID('tempdb.dbo.#results')
--convert table to XML PATH, ELEMENTS XSINIL is used to include NULL values
SET @htmlTD = (SELECT * FROM #results FOR XML PATH('TR'), ELEMENTS XSINIL)
--convert the way ELEMENTS XSINIL display NULL to display word NULL
SET @htmlTD = REPLACE(@htmlTD, ' xsi:nil="true"/>', '>NULL</TD>')
SET @htmlTD = REPLACE(@htmlTD, '<TR xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">', '<TR>')
--FOR XML PATH will set tags for each column name, <columnName1>abc</columnName1><columnName2>def</columnName2>
--this will replace all the column names with TD (html table data tag)
SELECT @htmlTD = REPLACE(REPLACE(@htmlTD, '<' + name + '>', '<TD>'), '</' + name + '>', '</TD>')
FROM tempdb.sys.columns WHERE object_id = OBJECT_ID('tempdb.dbo.#results')
SELECT '<TABLE cellpadding="2" cellspacing="2" border="1">'
+ '<TR>' + @htmlTH + '</TR>'
+ @htmlTD
+ '</TABLE>'
回答by FA LE TI NO
Suppose someone found his way here and does not understand the usage of the marked answer SQL, please read mine... it is edited and works. Table:staff, columns:staffname,staffphone and staffDOB
假设有人在这里找到了他的方式并且不理解标记答案 SQL 的用法，请阅读我的......它已被编辑并有效。表：员工，列：员工姓名、员工电话和员工DOB
declare @body varchar(max)
-- Create the body
set @body = cast( (
select td = dbtable + '</td><td>' + cast( phone as varchar(30) ) + '</td><td>' + cast( age as varchar(30) )
from (
select dbtable = StaffName ,
phone = staffphone,
age = datepart(day,staffdob)
from staff
group by staffname,StaffPhone,StaffDOB
) as d
for xml path( 'tr' ), type ) as varchar(max) )
set @body = '<table cellpadding="2" cellspacing="2" border="1">'
+ '<tr><th>Database Table</th><th>Entity Count</th><th>Total Rows</th></tr>'
+ replace( replace( @body, '<', '<' ), '>', '>' )
+ '<table>'
print @body
回答by Sandeep
I tried printing Multiple Tables using Mahesh Example above. Posting for convenience of others
我尝试使用上面的 Mahesh 示例打印多个表。发帖方便他人
USE MyDataBase
DECLARE @RECORDS_THAT_NEED_TO_SEND_EMAIL TABLE (ID INT IDENTITY(1,1),
POS_ID INT,
POS_NUM VARCHAR(100) NULL,
DEPARTMENT VARCHAR(100) NULL,
DISTRICT VARCHAR(50) NULL,
COST_LOC VARCHAR(100) NULL,
EMPLOYEE_NAME VARCHAR(200) NULL)
INSERT INTO @RECORDS_THAT_NEED_TO_SEND_EMAIL(POS_ID,POS_NUM,DISTRICT,COST_LOC,DEPARTMENT,EMPLOYEE_NAME)
SELECT uvwpos.POS_ID,uvwpos.POS_NUM,uvwpos.DISTRICT, uvwpos.COST_LOC,uvwpos.DEPARTMENT,uvemp.LAST_NAME + ' ' + uvemp.FIRST_NAME
FROM uvwPOSITIONS uvwpos LEFT JOIN uvwEMPLOYEES uvemp
on uvemp.POS_ID=uvwpos.POS_ID
WHERE uvwpos.ACTIVE=1 AND uvwpos.POS_NUM LIKE 'sde%'AND (
(RTRIM(LTRIM(LEFT(uvwpos.DEPARTMENT,LEN(uvwpos.DEPARTMENT)-1))) <> RTRIM(LTRIM(uvwpos.COST_LOC)))
OR (uvwpos.DISTRICT IS NULL)
OR (uvwpos.COST_LOC IS NULL) )
DECLARE @RESULT_DISTRICT_ISEMPTY varchar(4000)
DECLARE @RESULT_COST_LOC_ISEMPTY varchar(4000)
DECLARE @RESULT_COST_LOC__AND_DISTRICT_NOT_MATCHING varchar(4000)
DECLARE @BODY NVARCHAR(MAX)
DECLARE @HTMLHEADER VARCHAR(100)
DECLARE @HTMLFOOTER VARCHAR(100)
SET @HTMLHEADER='<html><body>'
SET @HTMLFOOTER ='</body></html>'
SET @RESULT_DISTRICT_ISEMPTY = '';
SET @BODY =@HTMLHEADER+ '<H3>PositionNumber where District is Empty.</H3>
<table border = 1>
<tr>
<th> POS_ID </th> <th> POS_NUM </th> <th> DEPARTMENT </th> <th> DISTRICT </th> <th> COST_LOC </th></tr>'
SET @RESULT_DISTRICT_ISEMPTY = CAST(( SELECT [POS_ID] AS 'td','',RTRIM([POS_NUM]) AS 'td','',
ISNULL(LEFT(DEPARTMENT,LEN(DEPARTMENT)-1),' ') AS 'td','', ISNULL([DISTRICT],' ') AS 'td','',ISNULL([COST_LOC],' ') AS 'td'
FROM @RECORDS_THAT_NEED_TO_SEND_EMAIL
WHERE DISTRICT IS NULL
FOR XML PATH('tr'), ELEMENTS ) AS VARCHAR(MAX))
SET @BODY = @BODY + @RESULT_DISTRICT_ISEMPTY +'</table>'
DECLARE @RESULT_COST_LOC_ISEMPTY_HEADER VARCHAR(400)
SET @RESULT_COST_LOC_ISEMPTY_HEADER ='<H3>PositionNumber where COST_LOC is Empty.</H3>
<table border = 1>
<tr>
<th> POS_ID </th> <th> POS_NUM </th> <th> DEPARTMENT </th> <th> DISTRICT </th> <th> COST_LOC </th></tr>'
SET @RESULT_COST_LOC_ISEMPTY = CAST(( SELECT [POS_ID] AS 'td','',RTRIM([POS_NUM]) AS 'td','',
ISNULL(LEFT(DEPARTMENT,LEN(DEPARTMENT)-1),' ') AS 'td','', ISNULL([DISTRICT],' ') AS 'td','',ISNULL([COST_LOC],' ') AS 'td'
FROM @RECORDS_THAT_NEED_TO_SEND_EMAIL
WHERE COST_LOC IS NULL
FOR XML PATH('tr'), ELEMENTS ) AS VARCHAR(MAX))
SET @BODY = @BODY + @RESULT_COST_LOC_ISEMPTY_HEADER+ @RESULT_COST_LOC_ISEMPTY +'</table>'
DECLARE @RESULT_COST_LOC__AND_DISTRICT_NOT_MATCHING_HEADER VARCHAR(400)
SET @RESULT_COST_LOC__AND_DISTRICT_NOT_MATCHING_HEADER='<H3>PositionNumber where Department and Cost Center are Not Macthing.</H3>
<table border = 1>
<tr>
<th> POS_ID </th> <th> POS_NUM </th> <th> DEPARTMENT </th> <th> DISTRICT </th> <th> COST_LOC </th></tr>'
SET @RESULT_COST_LOC__AND_DISTRICT_NOT_MATCHING = CAST(( SELECT [POS_ID] AS 'td','',RTRIM([POS_NUM]) AS 'td','',
ISNULL(LEFT(DEPARTMENT,LEN(DEPARTMENT)-1),' ') AS 'td','', ISNULL([DISTRICT],' ') AS 'td','',ISNULL([COST_LOC],' ') AS 'td'
FROM @RECORDS_THAT_NEED_TO_SEND_EMAIL
WHERE
(RTRIM(LTRIM(LEFT(DEPARTMENT,LEN(DEPARTMENT)-1))) <> RTRIM(LTRIM(COST_LOC)))
FOR XML PATH('tr'), ELEMENTS ) AS VARCHAR(MAX))
SET @BODY = @BODY + @RESULT_COST_LOC__AND_DISTRICT_NOT_MATCHING_HEADER+ @RESULT_COST_LOC__AND_DISTRICT_NOT_MATCHING +'</table>'
SET @BODY = @BODY + @HTMLFOOTER
USE DDDADMINISTRATION_DB
--SEND EMAIL
exec DDDADMINISTRATION_DB.dbo.uspSMTP_NOTIFY_HTML
@EmailSubject = 'District,Department & CostCenter Discrepancies',
@EmailMessage = @BODY,
@ToEmailAddress = '[email protected]',
@FromEmailAddress = '[email protected]'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MY POROFILE', -- replace with your SQL Database Mail Profile
@body = @BODY,
@body_format ='HTML',
@recipients = '[email protected]', -- replace with your email address
@subject = 'District,Department & CostCenter Discrepancies' ;
回答by bd33
All the other answers use variables and SET operations. Here's a way to do it within a select statement. Just drop this in as a column in your existing select.
所有其他答案都使用变量和 SET 操作。这是在 select 语句中执行此操作的一种方法。只需将其作为现有选择中的列放入即可。
(SELECT
'<table style=''font-family:"Verdana"; font-size: 10pt''>'
+ '<tr bgcolor="#9DBED4"><th>col1</th><th>col2</th><th>col3</th><th>col4</th><th>col5</th></tr>'
+ replace( replace( body, '<', '<' ), '>', '>' )
+ '</table>'
FROM
(
select cast( (
select td = cast(col1 as varchar(5)) + '</td><td align="right">' + col2 + '</td><td>' + col3 + '</td><td align="right">' + cast(col4 as varchar(5)) + '</td><td align="right">' + cast(col5 as varchar(5)) + '</td>'
from (
select col1 = col1,
col2 = col2,
col3 = col3,
col4 = col4,
col5 = col5
from m_LineLevel as onml
where onml.pkey = oni.pkey
) as d
for xml path( 'tr' ), type ) as varchar(max) ) as body
) as bodycte) as LineTable
