Konvertieren Sie eine SQL-Abfrageergebnistabelle in eine HTML-Tabelle für E-Mail

Ich führe eine SQL-Abfrage aus, die eine Ergebnistabelle zurückgibt. Ich möchte die Tabelle in einer E-Mail mit dbo.sp_send_dbMail senden.

Gibt es in SQL eine einfache Möglichkeit, eine Tabelle in eine HTML-Tabelle umzuwandeln? Derzeit konstruiere ich es manuell mit COALESCE und füge die Ergebnisse in ein Varchar ein, das ich als emailBody verwende.

Gibt es einen besseren Weg, dies zu tun?

Ich habe eine dynamische Prozedur erstellt, die jede zufällige Abfrage in eine HTML-Tabelle umwandelt, sodass Sie keine Spalten wie in den anderen Antworten fest codieren müssen.

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

Verwendung:

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;

Verwandte: Hier ist ein ähnlicher Code, um eine beliebige Abfrage in eine CSV-Zeichenfolge umzuwandeln.

Hier ist eine Möglichkeit, dies aus einem Artikel mit dem Titel “Formatieren Sie die Abfrageausgabe in eine HTML-Tabelle – der einfache Weg [archive]“. Sie müssten die Details Ihrer eigenen Abfrage durch die in diesem Beispiel ersetzen, die eine Liste von Tabellen und eine Zeilenanzahl erhält.

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

Sobald du hast @bodykönnen Sie dann einen beliebigen E-Mail-Mechanismus verwenden.

Das könnte Ihnen eine Vorstellung geben –

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

Hier unten mein häufig verwendetes Skript. Ich verwende dies, um Skripte auf zwei Tabellen/Ansichten mit einem SQL-Job auszuführen und die Ergebnisse als zwei HTML-Tabellen per E-Mail zu senden. Natürlich sollten Sie ein E-Mail-Profil erstellen, bevor Sie dies ausführen.

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

basierend auf JustinStolle-Code (danke), wollte ich eine Lösung, die generisch sein könnte, ohne die Spaltennamen angeben zu müssen.

Dieses Beispiel verwendet die Daten einer temporären Tabelle, kann aber natürlich nach Bedarf angepasst werden.

Hier ist, was ich bekommen habe:

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

Angenommen, jemand hat sich hierher verirrt und versteht die Verwendung der markierten Antwort-SQL nicht, bitte lesen Sie meine … es ist bearbeitet und funktioniert. Tabelle:Personal, Spalten:Personalname,PersonalTelefon und PersonalGeburtstag

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

Ich habe versucht, mehrere Tabellen mit dem obigen Mahesh-Beispiel zu drucken. Veröffentlichung zur Bequemlichkeit anderer

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" ;