Преобразование таблицы результатов SQL-запроса в таблицу HTML для электронной почты

Я запускаю SQL-запрос, который возвращает таблицу результатов. Я хочу отправить таблицу по электронной почте с помощью dbo.sp_send_dbMail.

Есть ли простой способ в SQL, чтобы превратить таблицу в таблицу HTML? В настоящее время я вручную создаю его с помощью COALESCE и помещая результаты в varchar, который я использую в качестве mailBody.

Есть ли лучший способ сделать это?

Ответ 1

Вот один из способов сделать это из статьи под названием " Форматировать вывод запроса в 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, '&lt;', '<' ), '&gt;', '>' )
          + '</table>'

print @body

Как только у вас есть @body, вы можете использовать любой механизм электронной почты, который вам нужен.

Ответ 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'', @[email protected] 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', @[email protected] 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.

Ответ 3

Это может дать вам некоторую идею -

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

Ответ 4

на основе кода 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>'

Ответ 5

Предположим, что кто-то нашел свой путь здесь и не понимает использование выделенного ответа 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, '&lt;', '<' ), '&gt;', '>' )
              + '<table>'
print @body

Ответ 6

Я попробовал распечатать несколько таблиц, используя вышеприведенный пример 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 [email protected]+ '<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' ;

Ответ 7

Все остальные ответы используют переменные и операции 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, '&lt;', '<' ), '&gt;', '>' )
                    + '</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

Ответ 8

Здесь мой общий используемый скрипт ниже. Я использую это для запуска сценариев в двух таблицах/представлениях с заданием 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

Ответ 9

Результат таблицы SQL через таблицу HTML