将 SQL 查询结果表转换为电子邮件的 HTML 表

Question

我正在运行一个返回结果表的 SQL 查询。我想使用 dbo.sp_send_dbMail 通过电子邮件发送该表。

SQL 中有没有一种直接的方法可以将表格转换为 HTML 表格？目前，我正在使用 COALESCE 手动构建它，并将结果放入我用作 emailBody 的 varchar 中。

有更好的方法吗？

Answer 1

这是我的实现：任何查询结果到 html 表。

我正在创建一些辅助程序来实现这一目标。这些帮助程序非常灵活，并且可以在各种上下文中重用。

• fnValidateDynamicSql - 验证传递的动态语句
• spAlterTblByRs - 将任何 SQL 语句结果保存到 #table。允许从代码
• spQueryResultAsHtmlTable 中完全删除动态 SQL - 从任何传递的 SQL 语句创建 html 表

享受:)

CREATE FUNCTION [dbo].[fnValidateDynamicSql]
    (@Sql NVARCHAR(MAX),     /* dynamic sql statement */
     @Params NVARCHAR(MAX)   /* parameters, if dynamic SQL is parametrized. Pass NULL if there are no params */
    )
RETURNS NVARCHAR(MAX)
AS
    /*  Check or @Sql statement is valid
     *  Returns NULL if valid, exception message otherwise   
     */
BEGIN
    DECLARE @Result VARCHAR(1000);

    IF EXISTS (SELECT NULL
               FROM [sys].[dm_exec_describe_first_result_set](@Sql, @Params, 0)
               WHERE [error_message] IS NOT NULL
                 AND [error_number] IS NOT NULL
                 AND [error_severity] IS NOT NULL
                 AND [error_state] IS NOT NULL
                 AND [error_type] IS NOT NULL
                 AND [error_type_desc] IS NOT NULL)
    BEGIN
        SELECT @Result = [error_message]
        FROM [sys].[dm_exec_describe_first_result_set](@Sql, @Params, 0)
        WHERE [column_ordinal] = 0;
    END;
    
    IF NULLIF(LTRIM(RTRIM(@Sql)), '') IS NULL 
       SET @Result = '@Sql is NULL';

    RETURN @Result;
END;
GO

CREATE PROCEDURE [dbo].[spAlterTblByRs]
    @ErrCode  INT OUT,
    @ErrMsg   VARCHAR(4000) OUT,    
    @Sql      NVARCHAR(MAX),         /* Query stmt  */
    @Params   NVARCHAR(MAX) = NULL,  /* Query parameters (like in sp_executesql) */
    @Tbl      NVARCHAR(256),         /* Table name */
    @DummyCol NVARCHAR(256),         /* Dummy column name (will be removed) */
    @PopulateTable BIT = NULL        /* If 1, then populate altered table by @Sql query data */ 
AS
    /* Alters table by recordset to be used. Populates data, if required. */    
BEGIN
    SET NOCOUNT ON;
    SET ARITHABORT ON;

    BEGIN TRY

        DECLARE @ERR_CODE_OK        INT =   0
            ,   @ERR_CODE_FAILURE   INT =   50000;

        SET @ErrCode = @ERR_CODE_OK;

        
        IF NULLIF(LTRIM(RTRIM(@Tbl)), '') IS NULL THROW @ERR_CODE_FAILURE, '@Tbl is empty', 1;
        IF NULLIF(LTRIM(RTRIM(@DummyCol)), '') IS NULL THROW @ERR_CODE_FAILURE, '@DummyCol is empty', 1;


        IF [dbo].[fnValidateDynamicSql](@Sql, @Params) IS NOT NULL
        BEGIN
            SET @ErrMsg = 'Invalid @Sql received: ' + [dbo].[fnValidateDynamicSql](@Sql, @Params);
            ;THROW @ERR_CODE_FAILURE, @ErrMsg, 1;
        END;


        DECLARE @AlterStmt      NVARCHAR(MAX) = SPACE(0);
        DECLARE @RemColStmt     NVARCHAR(MAX) = SPACE(0);   
             
        --  prepare existing table alter Stmt by previuos rs structure
        SET @AlterStmt = 'ALTER TABLE ' + @tbl + ' ADD ' + CHAR(13);

        ;WITH [rsStructure] AS (
            SELECT
                    [name]
                ,   [system_type_name]
                ,   [is_nullable]            
            FROM [sys].[dm_exec_describe_first_result_set](
                            @Sql
                        ,   @Params
                        ,   0
            )       
        )
        SELECT 
             @AlterStmt += QUOTENAME([name]) + SPACE(1) + [system_type_name] + IIF([is_nullable] = 0, ' NOT NULL' , SPACE(0)) + ',' + CHAR(13)
        FROM [rsStructure];

        SET @AlterStmt = LEFT(@AlterStmt, LEN(@AlterStmt) - 2);

        --  finally update table structure
        EXEC [sys].[sp_executesql] @AlterStmt; 
        

        --  remove dummy column
        SET @RemColStmt = 'ALTER TABLE ' + @tbl + ' DROP COLUMN ' + @DummyCol;
        EXEC [sys].[sp_executesql] @RemColStmt; 

        --  populate table with @Sql statement data
        IF @PopulateTable = 1
        BEGIN
            EXEC('INSERT INTO ' + @tbl + ' ' + @sql);
        END;
        
        
    END TRY
    BEGIN CATCH
    
        /* Use some error formatting sp instead */
        SELECT  @ErrCode    =   ERROR_NUMBER()
            ,   @ErrMsg     =   ERROR_MESSAGE();

    END CATCH

    RETURN @ErrCode;

END


GO

GO
CREATE PROCEDURE [dbo].[spQueryResultAsHtmlTable]
    @ErrCode    INT             OUT
,   @ErrMsg     NVARCHAR(4000)  OUT
,   @Sql        NVARCHAR(MAX)
,   @Params     NVARCHAR(MAX)
,   @HtmlTable  NVARCHAR(MAX)   OUT
AS
/*  Makes Html table by result, returned by provided @Query 
 */
BEGIN
    
    SET NOCOUNT ON;
    SET ARITHABORT ON;
        
    BEGIN TRY

        DECLARE @ERR_CODE_OK        INT =   0
            ,   @ERR_CODE_FAILED    INT =   50000;          

        SET @ErrCode = @ERR_CODE_OK;

        DECLARE @HtmlAsHml  XML
            ,   @ColumnList NVARCHAR(MAX)   =   SPACE(0);       

        IF NULLIF(LTRIM(RTRIM(@Sql)), SPACE(0)) IS NULL THROW @ERR_CODE_FAILED, 'Empty @Query received', 1;
                    
                
        IF OBJECT_ID('tempdb..#QueryResult') IS NOT NULL DROP TABLE [#QueryResult];
        CREATE TABLE [#QueryResult] ([dummy_col] BIT);

        EXEC [dbo].[spAlterTblByRs]
            @ErrCode        =   @ErrCode        OUT
        ,   @ErrMsg         =   @ErrMsg         OUT
        ,   @Sql            =   @Sql            
        ,   @Params         =   @Params         
        ,   @Tbl            =   '#QueryResult'  
        ,   @DummyCol       =   'dummy_col'     
        ,   @PopulateTable  =   1;

        IF @ErrCode <> 0 THROW @ErrCode, @ErrMsg, 1;
                    
            
        SELECT @ColumnList += IIF([column_ordinal] = 1, SPACE(0), ',') + '[td] = [' + [name] + ']'
        FROM [sys].[dm_exec_describe_first_result_set](
            @Sql    /*  @tsql                       */
        ,   @Params /*  @params                     */
        ,   0       /*  @browse_information_mode    */      
        )       
        ORDER BY [column_ordinal] ASC;


        DECLARE @h  XML
        ,       @d  XML;    

        /* Prepare headers */
        ;WITH [headers] AS (
            SELECT [h] = CONVERT(XML, (SELECT 
                [th] = [name] 
            FROM [sys].[dm_exec_describe_first_result_set](
                @Sql    /*  @tsql                       */
            ,   @Params /*  @params                     */
            ,   0       /*  @browse_information_mode    */      
            )   
            ORDER BY [column_ordinal] ASC
            FOR XML PATH(''), ROOT('tr')))
        )
        SELECT @h = [h] FROM [headers];
            
        
        /* Prepare rows */
        SET @sql = N'
        ;WITH [data] AS (
            SELECT [d] = (SELECT    
                ' + @ColumnList + '
            FROM [#QueryResult] 
            FOR XML RAW (''tr''), ELEMENTS XSINIL, TYPE)
        )       
        SELECT @d = [d] FROM [data]';

        SET @params = N'@d xml output';

        EXECUTE [sp_executesql] 
            @stmt   =   @sql
        ,   @params =   @params     
        ,   @d      =   @d      OUTPUT;
        

        /* Make table html */
        SET  @HtmlAsHml = CONVERT(XML, (SELECT [*] = @h, [*] = @d FOR XML PATH('table')));

        SET @HtmlAsHml.modify('insert attribute cellpadding {"2"} into (table)[1]')
        SET @HtmlAsHml.modify('insert attribute cellspacing {"2"} into (table)[1]')
        SET @HtmlAsHml.modify('insert attribute border {"1"} into (table)[1]')
    
        
        /* Prepare value to be returned */
        SET @HtmlTable = CONVERT(NVARCHAR(MAX), @HtmlAsHml);
        
               
    END TRY
    BEGIN CATCH             

        /* Use some error formatting sp instead */
        SELECT  @ErrCode    =   ERROR_NUMBER()
            ,   @ErrMsg     =   ERROR_MESSAGE();

    END CATCH;
    
    RETURN @ErrCode;
    
END;
GO

/* Usage */

DECLARE
    @ErrCode    INT             
,   @ErrMsg     NVARCHAR(4000)  
,   @Sql        NVARCHAR(MAX)   =   'select top (10) * from sys.tables'
,   @HtmlTable  NVARCHAR(MAX);

EXEC [dbo].[spQueryResultAsHtmlTable]
    @ErrCode    =   @ErrCode    OUT
,   @ErrMsg     =   @ErrMsg     OUT
,   @Sql        =   @Sql        
,   @Params     =   NULL        
,   @HtmlTable  =   @HtmlTable  OUT; /* YOur desired html table here */

IF @ErrCode <> 0 THROW @ErrCode, @ErrMsg, 1;

Answer 2

我制作了一个动态过程，它将任何随机查询转换为 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

用法：

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;

相关：这里是类似的代码，可将任意查询转换为 CSV 字符串。

Answer 3

这是一篇标题为“将查询输出格式化为 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

一旦您拥有@body，您就可以使用您想要的任何电子邮件机制。

Answer 4

这可能会给你一些想法——

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

Answer 5

下面是我常用的脚本。我使用它通过 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

Answer 6

JustinStolle 的答案以不同的方式。一些注意事项：

• print 语句可能会将字符串截断为 4000 个字符，但我的测试字符串的长度为 9520 个字符。
• [tr/th]表示层次结构，例如...。
• [@name] 将字段添加为 XML 属性。
• MS SQL XML 连接同名的字段，因此字段之间的 null 可以防止这种情况发生。

---

declare @body nvarchar(max)

select @body = cast((
    select N'2' [@cellpadding], N'2' [@cellspacing], N'1' [@border],
           N'Database Table' [tr/th], null [tr/td],
           N'Entity Count' [tr/th], null [tr/td],
           N'Total Rows' [tr/th], null,
           (select  object_name( object_id ) [td], null,
                    count( distinct name ) [td], null,
                    count( * )  [td], null
             from sys.columns
             group by object_name( object_id )
             for xml path('tr'), type)
        for xml path('table'), type
        ) as nvarchar(max))

print @body  -- only shows up to 4000 characters depending

Answer 7

基于 JustinStolle 代码（谢谢），我想要一个通用的解决方案，而无需指定列名。

该示例使用临时表的数据，但当然可以根据需要进行调整。

这是我得到的：

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>'

Answer 8

假设有人找到了这里的方法并且不理解标记的答案 SQL 的用法，请阅读我的...它已编辑并且可以工作。表：员工，列：员工姓名、员工电话和员工出生日期

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

Answer 9

我尝试使用上面的 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' ;

Answer 10

所有其他答案都使用变量和 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

Answer 11

接下来的一段代码，我准备生成用于文档的 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>'