从存储过程中写出HTML并通过电子邮件发送

My boss insists I send him a daily status report "the hacker way" by outputing HTML from a stored procedure and sending the return to his email using Database mailer. My colleague and I both agree that we should be using SSRS for this matter but since we aren't the ones with the money we unfortunately have to get stuck doing it this way. I myself have never seen this done and am having some formatting issues getting my table to format with cells. Can anyone shed some light on how to get this to work?

我的老板坚持要通过从存储过程输出HTML并使用数据库邮件程序将回复发送到他的电子邮件，向他发送每日状态报告“黑客方式”。我的同事和我都同意我们应该在这个问题上使用SSRS，但由于我们不是那些有钱的人，所以不幸的是我们不得不这样做。我自己从来没有看到这样做，并有一些格式问题让我的表格格式化。任何人都可以阐明如何使这个工作？

BEGIN
DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<b>Shots Statistics for ' + convert(varchar, GETDATE()-1, 101) + ':</b>' +
    N'<table border="1" width="400">' +
    CAST ( 
              (SELECT 'Practice:' + PracticeName as Status, 'Aprima ID:'+ AprimaSiteID,
               'Daily Count:' + CAST(Daily AS nvarchar(5)), 'Monthly Count:' + CAST(Monthly AS nvarchar(5))
FROM [CriticalKeyDatabase].[dbo].[ShotsManagement]

                ORDER BY Status
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>';

    exec msdb.dbo.sp_send_dbmail @profile_name='aProfileName', @recipients='anEmail@Email.com', @body=@tableHTML, @subject='Daily Shots', @importance='High', @body_format = 'HTML'
END

GO

I am basing this off a previous employee he had who gave him the idea. I don't see any declarations for cells anywhere in his query. I am not the best at SQL either..

我的基础是他曾经给过他的想法的前任员工。我在查询中的任何地方都没有看到任何单元格声明。我也不是最好的SQL ..

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<b>Message Statistics for ' + convert(varchar, GETDATE()-1, 101) + ':</b>' +
    N'<table border="1" width="400">' +
    CAST ( ( SELECT td = Status,       ' ',
                    td = convert(varchar, StatusValue) + '',       ' '
              FROM 
              (SELECT 'Unsent Messages' as Status, Count(*) as StatusValue
  FROM [CriticalKeyDatabase].[dbo].[AllJobs_V]
  where convert(varchar, CreateDate, 101) = convert(varchar, GETDATE()-1, 101) and ReadyToSend = 1
UNION
SELECT 'Total Messages' as Status, Count(*) as StatusValue
  FROM [CriticalKeyDatabase].[dbo].[AllJobs_V]
  where convert(varchar, CreateDate, 101) = convert(varchar, GETDATE()-1, 101)) A

                ORDER BY StatusValue
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' +

    N'<b>Sender Statistics for ' + convert(varchar, GETDATE()-1, 101) + ':</b>' +
    N'<table border="1" width="400">' +
    CAST ( ( SELECT td = Status,       ' ',
                    td = convert(varchar, StatusValue) + '',       ' '
              FROM 
              (SELECT 'Sender: ' + Requestor as Status, Count(*) as StatusValue
FROM [CriticalKeyDatabase].[dbo].[AllJobs_V]
where convert(varchar, CreateDate, 101) = convert(varchar, GETDATE()-1, 101)
group by Requestor) A

                ORDER BY Status
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' +

    N'<b>Recipient Statistics for ' + convert(varchar, GETDATE()-1, 101) + ':</b>' +
    N'<table border="1" width="400">' +
    CAST ( ( SELECT td = Status,       ' ',
                    td = convert(varchar, StatusValue) + '',       ' '
              FROM 
              (SELECT 'Recipient: ' + Recipient as Status, Count(*) as StatusValue
FROM [CriticalKeyDatabase].[dbo].[AllJobs_V]
where convert(varchar, CreateDate, 101) = convert(varchar, GETDATE()-1, 101)
group by Recipient) A
                                ORDER BY Status
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' +

    N'<b>Event Statistics for ' + convert(varchar, GETDATE()-1, 101) + ':</b>' +
    N'<table border="1" width="400">' +
    CAST ( ( SELECT td = Status,       ' ',
                    td = convert(varchar, StatusValue) + '',       ' '
              FROM 
              (SELECT 'Event: ' + EventType as Status, Count(*) as StatusValue
FROM [CriticalKeyDatabase].[dbo].[AllJobs_V]
where convert(varchar, CreateDate, 101) = convert(varchar, GETDATE()-1, 101)
group by EventType) A

                ORDER BY Status
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>';

    exec msdb.dbo.sp_send_dbmail @profile_name='Localhost', @recipients='someemailAddresses@email.com', @body=@tableHTML, @subject='Daily Statistics', @importance='High', @body_format = 'HTML'
END

The previous employee's reports looked like this.
从存储过程中写出HTML并通过电子邮件发送

之前员工的报告看起来像这样。

But mine are coming out like this.
从存储过程中写出HTML并通过电子邮件发送

但是我这样出来了。

2 个解决方案

#1

All of the usual stuff about that being a bad practice aside, what your ex-coworker seems to be doing is leveraging SQL Server's native XML capabilities. You are not getting the "td" tags because they are not being assigned in your sub-query.

除了那些不好的做法之外的所有常见的东西，你的前同事似乎正在做的是利用SQL Server的原生XML功能。您没有获得“td”标记，因为它们未在您的子查询中分配。

If you look at his queries, you will see the "td = ..." constructs. The reason they work is because the sub-query is being treated as XML (due to the FOR XML PATH construct) and thus the "td = " clauses are being mapped to XML nodes.

如果查看他的查询，您将看到“td = ...”结构。它们工作的原因是因为子查询被视为XML（由于FOR XML PATH构造），因此“td =”子句被映射到XML节点。

Try adding that to your code and see if you get the proper table cells...

尝试将其添加到您的代码中，看看您是否获得了正确的表格单元格...

BEGIN
DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<b>Shots Statistics for ' + convert(varchar, GETDATE()-1, 101) + ':</b>' +
    N'<table border="1" width="400">' +
    CAST ( 
              (SELECT td = 'Practice:' + PracticeName as Status, 'Aprima ID:'+ AprimaSiteID,
               td = 'Daily Count:' + CAST(Daily AS nvarchar(5)), 'Monthly Count:' + CAST(Monthly AS nvarchar(5))
FROM [CriticalKeyDatabase].[dbo].[ShotsManagement]

                ORDER BY Status
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>';

    exec msdb.dbo.sp_send_dbmail @profile_name='aProfileName', @recipients='anEmail@Email.com', @body=@tableHTML, @subject='Daily Shots', @importance='High', @body_format = 'HTML'
END

GO

#2

This will htmlify and email the contents of a #temp table (including column names). All of the styling is done with CSS, so just change @style if you want it to look different. If you want to include multiple tables, you can modify it to add @table2, @table3, etc.

这将htmlify并通过电子邮件发送#temp表的内容（包括列名）。所有的样式都是用CSS完成的，所以如果你希望它看起来不同，只需更改@style。如果要包含多个表，可以修改它以添加@ table2，@ table3等。

Example use:

使用示例：

SELECT *
INTO #email_data
FROM <yadda yadda>

EXEC [dbo].[email_table]
  @tablename  = '#email_data'
 ,@recipients = 'me@company.com; boss@company.com;'
 ,@subject    = 'TPS Reports'

Scripts

脚本

CREATE PROCEDURE [dbo].[table_to_html] (
  @tablename sysname,
  @html xml OUTPUT,
  @order varchar(4) = 'ASC'
) AS
BEGIN
  DECLARE
    @sql nvarchar(max),
    @cols nvarchar(max),
    @htmlcols xml,
    @htmldata xml,
    @object_id int = OBJECT_ID('[tempdb].[dbo].'+QUOTENAME(@tablename));

  IF @order <> 'DESC' SET @order = 'ASC';

  SELECT @cols = COALESCE(@cols+',','')+QUOTENAME([name])+' '+@order
  FROM tempdb.sys.columns
  WHERE object_id = @object_id
  ORDER BY [column_id];

  SET @htmlcols = (
    SELECT [name] AS [th]
    FROM tempdb.sys.columns
    WHERE object_id = @object_id
    ORDER BY [column_id] FOR XML PATH(''),ROOT('tr')
  );

  SELECT @sql = COALESCE(@sql+',','SELECT @htmldata = (SELECT ')+'ISNULL(LTRIM('+QUOTENAME([name])+'),''NULL'') AS [td]'
  FROM tempdb.sys.columns
  WHERE object_id = @object_id
  ORDER BY [column_id];

  SET @sql = @sql + ' FROM '+QUOTENAME(@tablename)+' ORDER BY '+@cols+' FOR XML RAW(''tr''), ELEMENTS)';

  EXEC sp_executesql @sql, N'@htmldata xml OUTPUT', @htmldata OUTPUT

  SET @html = (SELECT @htmlcols,@htmldata FOR XML PATH('table'));
END
GO

CREATE PROCEDURE [dbo].[email_table] (
  @tablename sysname,
  @recipients nvarchar(max),
  @subject nvarchar(max) = '',
  @order varchar(4) = 'ASC'
) AS
BEGIN
  IF OBJECT_ID('[tempdb].[dbo].'+QUOTENAME(@tablename)) IS NULL RAISERROR('Table does not exist. [dbo].[email_table] only works with temporary tables.',16,1);

  DECLARE @style varchar(max) = 'table {border-collapse:collapse;} td,th {white-space:nowrap;border:solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;} th {border-bottom-width:2px;}';

  DECLARE @table1 xml;
  EXEC [dbo].[table_to_html] @tablename, @table1 OUTPUT, @order;

  DECLARE @email_body AS nvarchar(max) = (
    SELECT
      (SELECT
        @style AS [style]
       FOR XML PATH('head'),TYPE),
      (SELECT
        @table1
       FOR XML PATH('body'),TYPE)
    FOR XML PATH('html')
  );

  EXEC msdb.dbo.sp_send_dbmail
    @recipients = @recipients,
    @subject = @subject,
    @body = @email_body,
    @body_format = 'html';

END
GO

#1