unir - Convertir una tabla de resultados de consultas SQL en una tabla HTML para correo electrónico

unir dos campos en sql

Question

Estoy ejecutando una consulta SQL que devuelve una tabla de resultados. Quiero enviar la tabla en un correo electrónico usando dbo.sp_send_dbMail.

¿Existe una forma sencilla dentro de SQL para convertir una tabla en una tabla HTML? Actualmente, lo estoy construyendo manualmente usando COALESCE y poniendo los resultados en un varchar que uso como el correo electrónico.

¿Hay una mejor manera de hacer esto?

Answer 1

Aquí mi script usado más abajo. Utilizo esto para ejecutar scripts en dos tablas / vistas con trabajo SQL y envío resultados como dos tablas HTML por correo. Por supuesto, usted debe crear un perfil de correo antes de ejecutar esto.

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 2

Esta es una forma de hacerlo desde un artículo titulado " Dar formato a los resultados de la consulta en una tabla HTML: la forma más sencilla ". Debería sustituir los detalles de su propia consulta por los de este ejemplo, que obtiene una lista de tablas y un recuento de filas.

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

Una vez que tenga @body , puede usar el mecanismo de correo electrónico que desee.

Answer 3

Esto podría darte una 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

Answer 4

Hice un proceso dinámico que convierte cualquier consulta aleatoria en una tabla HTML, para que no tenga que escribir columnas de código duro como en las otras respuestas.

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

Uso:

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;

Relacionado: Aquí hay un código similar para convertir cualquier consulta arbitraria en una cadena CSV .

Answer 5

Intenté imprimir tablas múltiples usando el ejemplo de Mahesh anterior. Publicación para conveniencia de los demás.

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 6

Supongamos que alguien encontró su camino aquí y no entiende el uso de la respuesta marcada SQL, lea el mío ... está editado y funciona. Tabla: personal, columnas: nombre del personal, staffphone y staffDOB

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

Answer 7

Todas las otras respuestas usan variables y operaciones SET. Aquí hay una manera de hacerlo dentro de una declaración selecta. Simplemente suelte esto como una columna en su selección existente.

    (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

Answer 8

basado en el código de JustinStolle (gracias), quería una solución que pudiera ser genérica sin tener que especificar los nombres de las columnas.

Esta muestra utiliza los datos de una tabla temporal pero, por supuesto, se puede ajustar según sea necesario.

Aquí está lo que tengo:

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