CODEWITHSUNDEEP
htmlsql-servert-sql
JJ.
JJ.

Reputation: 9960

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

I have a table:

Select A, B, C 
FROM Table
WHERE Z = P 
AND Y = N

I want to capture the results and create an HTML table out of it, as such:

DECLARE @HTMLTable VARCHAR(MAX)

@HTMLTable = ???

A   B   C
xx  xxx xxxxx
x   xx  x
xx  x   xxx

And so on.

The reason for this is because I want to send this HTML table in an email.

How do I go about doing this?

Upvotes: 2

Views: 18389

Answers (3)

db9dreamer
db9dreamer

Reputation: 1715

create table #table (
    a varchar(50),
    b varchar(50),
    c varchar(50)
)
insert into #table values
    ('xx','xxx','xxxx'),
    ('aaa','b','cc'),
    ('xxxx','xx','xx xxxx')
go
DECLARE @HTMLTable VARCHAR(MAX)

set @HTMLTable = '<table><thead><tr><th>a</th><th>b</th><th>c</th></tr></thead><tbody>'

select @HTMLTable += '<tr><td>'+a+'</<td><td>'+b+'</<td><td>'+c+'</<td></tr>'
from #table

set @HTMLTable += '</tbody></table>'

print @HTMLTable

Upvotes: 2

Shridhar
Shridhar

Reputation: 2468

Create below procedure which will convert table result into html format

   create proc [dbo].[Proc_QueryToHtmlTable] (
@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

and then test as below

      declare @html NVARCHAR(MAX)=''
      exec Proc_QueryToHtmlTable '
     SELECT 1 as id, 2 as name  ',' name' ,@html OUTPUT
       print @html

    EXEC  msdb.dbo.sp_send_dbmail @profile_name = 'profile_name'
    ,@execute_query_database = 'Db NAME'
    ,@body = @html
    ,@body_format = 'HTML'
    ,@recipients = '***@gmail.com;**@gmail.com'
    ,@subject = 'test  Report'

Upvotes: 0

Joy Walker
Joy Walker

Reputation: 542

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

Upvotes: 9

Related Questions