Convert a SQL Query Result Table to an HTML Table for Email

Convert a SQL query result table to an HTML table for email

Here is one way to do it from an article titled "Format query output into an HTML table - the easy way [archive]". You would need to substitute the details of your own query for the ones in this example, which gets a list of tables and a row count.

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

Once you have @body, you can then use whatever email mechanism you want.

how do I convert a select statement result into an HTML table in SQL Server?

Make use of FOR XML PATH (learned from others here). something like this:

SET @tableHTML =
N'<table>' +
N'<tr><th>SpecialOfferID</th>
<th>Description</th>
<th>Type</th>
<th>Category</th>
<th>StartDate</th>
<th>EndDate</th>
</tr>' +
CAST ( (
SELECT td = CAST([SpecialOfferID] AS VARCHAR(100)),'',
td = [Description],'',
td = [Type],'',
td = [Category] ,'',
td = CONVERT(VARCHAR(30),[StartDate],120) ,'',
td = CONVERT(VARCHAR(30),[EndDate],120)
FROM [AdventureWorks].[Sales].[SpecialOffer]
ORDER BY [SpecialOfferID]
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'

Returning query to an HTML table alert email with TSQL dbmail

Because the requirement is to pass multiple rows to the body of the email, it would be best served in a tabular format.

This can be achieved by including in the email body, an HTML table of dossiers that meet the criteria.

Simply replace the column name's with the correct name's from table bo.

Let's break down the elements individually:

CREATE PROCEDURE dbo.DossierEmailSend
AS

Create a holding table for the dossiers that met the criteria

DECLARE @dossiers TABLE (col1 varchar(100), col2 varchar(100), col3 varchar(100), col4 varchar(100));

Insert the dossiers that met the criteria

INSERT INTO @dossiers
SELECT col1, col2, col3, convert(varchar,col4) col4  
    FROM bo
    WHERE nmdos LIKE '%preço%'
    AND datafinal = DATEADD(day, -1, CONVERT(date, GETDATE()))

Create a row check to determine whether to send the email or not

DECLARE @rows int;
SET @rows = (SELECT COUNT(*) FROM @dossiers)

Check if any dossiers met the criteria

IF @rows > 0 
BEGIN

Set the body elements

DECLARE @message varchar(1000);
-- declare the xml data to pass to the HTML body
DECLARE @xml NVARCHAR(MAX);
-- body will hold the HTML formatted table in the email
DECLARE @body NVARCHAR(MAX);

Create the columns that will hold each row of data as xml

SET @xml = CAST(( SELECT col1 AS 'td','',col2 AS 'td','', col3 AS 'td','', col4 AS 'td'
FROM @dossiers
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

Set the HTML for the body

SET @body ='<html><body><H3>Dossier Info</H3>
<table border = 1> 
<tr>
<th> col1 </th> <th> col2 </th> <th> col3 </th> <th> col4 </th></tr>'

Stitch everything together, appending the HTML table

SET @body = @body + @xml +'</table></body></html>'

SET NOCOUNT ON

Send the email and append the data table to the body

EXEC dbo.uspSendEmail 'Dossiers FOund', 'Convert a SQL Query Result Table to an HTML Table for Email@aaaa', @body, NULL, 'CC EMAIL 1'
SET NOCOUNT OFF

END

The finished solution should look something like the below:

CREATE PROCEDURE dbo.DossierEmailSend
AS
 --Create a holding table for the dossiers that met the criteria

DECLARE @dossiers TABLE (col1 varchar(100), col2 varchar(100), col3 varchar(100), col4 varchar(100));

 --Insert the dossiers that met the criteria

INSERT INTO @dossiers
SELECT col1, col2, col3, convert(varchar,col4) col4  
    FROM bo
    WHERE nmdos LIKE '%preço%'
    AND datafinal = DATEADD(day, -1, CONVERT(date, GETDATE()))

 --Create a row check to determine whether to send the email or not

DECLARE @rows int;
SET @rows = (SELECT COUNT(*) FROM @dossiers)

 --Check if any dossiers met the criteria

IF @rows > 0 
BEGIN

 --Set the body elements

DECLARE @message varchar(1000);
-- declare the xml data to pass to the HTML body
DECLARE @xml NVARCHAR(MAX);
-- body will hold the HTML formatted table in the email
DECLARE @body NVARCHAR(MAX);

 --Create the columns that will hold each row of data as xml

SET @xml = CAST(( SELECT col1 AS 'td','',col2 AS 'td','', col3 AS 'td','', col4 AS 'td'
FROM @dossiers
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

 --Set the HTML for the body

SET @body ='<html><body><H3>Dossier Info</H3>
<table border = 1> 
<tr>
<th> col1 </th> <th> col2 </th> <th> col3 </th> <th> col4 </th></tr>'

 --Stitch everything together, appending the HTML table

SET @body = @body + @xml +'</table></body></html>'

SET NOCOUNT ON 

 --Send the email and append the data table to the body

EXEC dbo.uspSendEmail 'Dossiers FOund', 'Convert a SQL Query Result Table to an HTML Table for Email@aaaa', @body, NULL, 'CC EMAIL 1'
SET NOCOUNT OFF

END

Convert SQL Server table into HTML using TSQL

There is a script created by Shnugo which can be found here with his comments, and methods to call it. I specifically use to build a HTML table when I need to send results with DBMAIL.

Here's how

/************************************************************************************************
Function to build a HTML table.

Created by Shnugo on SO: https://stackoverflow.com/a/39487565/6167855

Usage:

declare @body xml = 
    (select AdminTools.dbo.ufn_CreateHTMLTable (
    (select * from SomeTable for xml path ('row'),elements xsinil)
    ,null,null,null))

--CSS to make borders, which makes us change datatype
declare @body_html varchar(max)
set @body_html =  
    '<style type="text/css" media="screen,print">
    .center
    {
        text-align: center;
    }
    table,th
    {
        border: 1px solid black;
    }
    table,tr
    {
        border: 1px solid black;
    }
    table,td
    {
        border: 1px solid black;
    }
    </style>' 
    + cast(@body as varchar(max))

Then @body_html for sp_send_dbmail

************************************************************************************************/

CREATE FUNCTION [dbo].[ufn_CreateHTMLTable]
(
    @SelectForXmlPathRowElementsXsinil XML
   ,@tblClass VARCHAR(100) --NULL to omit this class
   ,@thClass VARCHAR(100)  --same
   ,@tbClass VARCHAR(100)  --same
)
RETURNS XML
AS
BEGIN

RETURN 
(
    SELECT @tblClass AS [@class]  
    ,@thClass AS [thead/@class]
    ,@SelectForXmlPathRowElementsXsinil.query(
              N'let $first:=/row[2]
                return 
                <tr> 
                {
                for $th in $first/*
                return <th>{if(not(empty($th/@caption))) then xs:string($th/@caption) else local-name($th)}</th>
                }
                </tr>') AS thead
    ,@tbClass AS [tbody/@class]
    ,@SelectForXmlPathRowElementsXsinil.query(
               N'for $tr in /row
                 return 
                 <tr>{$tr/@class}
                 {
                 for $td in $tr/*
                 return
                 if(empty($td/@link)) 
                 then <td>{$td/@class}{string($td)}</td>
                 else <td>{$td/@class}<a href="{$td/@link}">{string($td)}</a></td>
                 }
                 </tr>') AS tbody
    FOR XML PATH('table'),TYPE
) 
END

GO

Getting bad format on float values when exporting query to html table

you are using cast to show the values in the html-table:

SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''

MS-Help tells you more, but basically this is the automatic behavior of cast with a float value.

You should try to already convert your float-fields to the desired format in the query you pass, using the STR() function, see str function description

Convert a SQL Query Result Table to an HTML Table for Email