Convert a SQL query result table to an HTML table for email
I am running a SQL query that returns a tabl开发者_运维技巧e of results. I want to send the table in an email using dbo.sp_send_dbMail.
Is there a straightforward way within SQL to turn a table into an HTML table? Currently, I'm manually constructing it using COALESCE and putting the results into a varchar that I use as the emailBody.
Is there a better way to do this?
I made a dynamic proc which turns any random query into an HTML table, so you don't have to hardcode columns like in the other responses.
-- 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
Usage:
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 = 'bar@baz.com;',
@subject = 'HTML email',
@body = @html,
@body_format = 'HTML',
@query_no_truncate = 1,
@attach_query_result_as_file = 0;
Related: Here is similar code to turn any arbitrary query into a CSV string.
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.
This might give you some 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 = 'bruhaspathy@hotmail.com', -- replace with your email address
@subject = 'E-mail in Tabular Format' ;
DROP TABLE #Temp
Here my common used script below. I use this for running scripts on two tables/views with SQL job and send results as two HTML tables via mail. Ofcourse you should create mail profile before run this.
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= '<mailaddress@domain.com>',
@from_address = @mailfrom,
@reply_to = '<mailaddress@domain.com>',
@subject = @subject,
@body = @mailbody,
@body_format = 'HTML'
-- ,@importance = 'HIGH'
END
JustinStolle's answer in a different way. A few notes:
- The
printstatement may truncate the string to 4000 characters, but my test string for example was 9520 characters in length. - The
[tr/th]indicates hierarchy, e.g.,<tr><th>...</th></tr>. - The
[@name]adds fields as XML attributes. - MS SQL XML concatenates fields of the same name, so
nullin between fields prevents that.
declare @body nvarchar(max)
select @body = cast((
select N'2' [@cellpadding], N'2' [@cellspacing], N'1' [@border],
N'Database Table' [tr/th], null [tr/td],
N'Entity Count' [tr/th], null [tr/td],
N'Total Rows' [tr/th], null,
(select object_name( object_id ) [td], null,
count( distinct name ) [td], null,
count( * ) [td], null
from sys.columns
group by object_name( object_id )
for xml path('tr'), type)
for xml path('table'), type
) as nvarchar(max))
print @body -- only shows up to 4000 characters depending
based on JustinStolle code (thank you), I wanted a solution that could be generic without having to specify the column names.
This sample is using the data of a temp table but of course it can be adjusted as required.
Here is what I got:
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>'
Suppose someone found his way here and does not understand the usage of the marked answer SQL, please read mine... it is edited and works. Table:staff, columns:staffname,staffphone and 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, '<', '<' ), '>', '>' )
+ '<table>'
print @body
I tried printing Multiple Tables using Mahesh Example above. Posting for convenience of others
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 = 'Slohani@azdes.gov',
@FromEmailAddress = 'Slohani@azdes.gov'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MY POROFILE', -- replace with your SQL Database Mail Profile
@body = @BODY,
@body_format ='HTML',
@recipients = 'Recepients@internalmail.com', -- replace with your email address
@subject = 'District,Department & CostCenter Discrepancies' ;
All the other answers use variables and SET operations. Here's a way to do it within a select statement. Just drop this in as a column in your existing 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, '<', '<' ), '>', '>' )
+ '</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
Following piece of code, I have prepared for generating the HTML file for documentation which includes Table Name and Purpose in each table and Table Metadata information. It might be helpful!
use Your_Database_Name;
print '<!DOCTYPE html>'
PRINT '<html><body>'
SET NOCOUNT ON
DECLARE @tableName VARCHAR(30)
DECLARE tableCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT T.name AS TableName
FROM sys.objects AS T
WHERE T.type_desc = 'USER_TABLE'
ORDER BY T.name
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @tableName
WHILE @@FETCH_STATUS = 0 BEGIN
print '<table>'
print '<tr><td><b>Table Name: <b></td><td>'+@tableName+'</td></tr>'
print '<tr><td><b>Prupose: <b></td><td>????YOu can Fill later????</td></tr>'
print '</table>'
print '<table>'
print '<tr><th>ColumnName</th><th>DataType</th><th>Size</th><th>PrecScale</th><th>Nullable</th><th>Default</th><th>Identity</th><th>Remarks</th></tr>'
SELECT concat('<tr><td>',
LEFT(C.name, 30) /*AS ColumnName*/,'</td><td>',
LEFT(ISC.DATA_TYPE, 10) /*AS DataType*/,'</td><td>',
C.max_length /*AS Size*/,'</td><td>',
CAST(P.precision AS VARCHAR(4)) + '/' + CAST(P.scale AS VARCHAR(4)) /*AS PrecScale*/,'</td><td>',
CASE WHEN C.is_nullable = 1 THEN 'Null' ELSE 'No Null' END /*AS [Nullable]*/,'</td><td>',
LEFT(ISNULL(ISC.COLUMN_DEFAULT, ' '), 5) /*AS [Default]*/,'</td><td>',
CASE WHEN C.is_identity = 1 THEN 'Identity' ELSE '' END /*AS [Identity]*/,'</td><td></td></tr>')
FROM sys.objects AS T
JOIN sys.columns AS C ON T.object_id = C.object_id
JOIN sys.types AS P ON C.system_type_id = P.system_type_id and c.user_type_id = p.user_type_id
JOIN INFORMATION_SCHEMA.COLUMNS AS ISC ON T.name = ISC.TABLE_NAME AND C.name = ISC.COLUMN_NAME
WHERE T.type_desc = 'USER_TABLE'
AND T.name = @tableName
ORDER BY T.name, ISC.ORDINAL_POSITION
print '</table>'
print '</br>'
FETCH NEXT FROM tableCursor INTO @tableName
END
CLOSE tableCursor
DEALLOCATE tableCursor
SET NOCOUNT OFF
PRINT '</body></html>'
Here is my implementation: any query result to html table.
I'm creating some helper procedures to achieve this. These helper procedures are flexible, and may be reused in various contexts.
fnValidateDynamicSql- to validate passed dynamic statementspAlterTblByRs- to save any SQL statement result to#table. Allows to completely remove dynamic SQL from the codespQueryResultAsHtmlTable- creates html table from any passed SQL statement
Enjoy :)
CREATE FUNCTION [dbo].[fnValidateDynamicSql]
(@Sql NVARCHAR(MAX), /* dynamic sql statement */
@Params NVARCHAR(MAX) /* parameters, if dynamic SQL is parametrized. Pass NULL if there are no params */
)
RETURNS NVARCHAR(MAX)
AS
/* Check or @Sql statement is valid
* Returns NULL if valid, exception message otherwise
*/
BEGIN
DECLARE @Result VARCHAR(1000);
IF EXISTS (SELECT NULL
FROM [sys].[dm_exec_describe_first_result_set](@Sql, @Params, 0)
WHERE [error_message] IS NOT NULL
AND [error_number] IS NOT NULL
AND [error_severity] IS NOT NULL
AND [error_state] IS NOT NULL
AND [error_type] IS NOT NULL
AND [error_type_desc] IS NOT NULL)
BEGIN
SELECT @Result = [error_message]
FROM [sys].[dm_exec_describe_first_result_set](@Sql, @Params, 0)
WHERE [column_ordinal] = 0;
END;
IF NULLIF(LTRIM(RTRIM(@Sql)), '') IS NULL
SET @Result = '@Sql is NULL';
RETURN @Result;
END;
GO
CREATE PROCEDURE [dbo].[spAlterTblByRs]
@ErrCode INT OUT,
@ErrMsg VARCHAR(4000) OUT,
@Sql NVARCHAR(MAX), /* Query stmt */
@Params NVARCHAR(MAX) = NULL, /* Query parameters (like in sp_executesql) */
@Tbl NVARCHAR(256), /* Table name */
@DummyCol NVARCHAR(256), /* Dummy column name (will be removed) */
@PopulateTable BIT = NULL /* If 1, then populate altered table by @Sql query data */
AS
/* Alters table by recordset to be used. Populates data, if required. */
BEGIN
SET NOCOUNT ON;
SET ARITHABORT ON;
BEGIN TRY
DECLARE @ERR_CODE_OK INT = 0
, @ERR_CODE_FAILURE INT = 50000;
SET @ErrCode = @ERR_CODE_OK;
IF NULLIF(LTRIM(RTRIM(@Tbl)), '') IS NULL THROW @ERR_CODE_FAILURE, '@Tbl is empty', 1;
IF NULLIF(LTRIM(RTRIM(@DummyCol)), '') IS NULL THROW @ERR_CODE_FAILURE, '@DummyCol is empty', 1;
IF [dbo].[fnValidateDynamicSql](@Sql, @Params) IS NOT NULL
BEGIN
SET @ErrMsg = 'Invalid @Sql received: ' + [dbo].[fnValidateDynamicSql](@Sql, @Params);
;THROW @ERR_CODE_FAILURE, @ErrMsg, 1;
END;
DECLARE @AlterStmt NVARCHAR(MAX) = SPACE(0);
DECLARE @RemColStmt NVARCHAR(MAX) = SPACE(0);
-- prepare existing table alter Stmt by previuos rs structure
SET @AlterStmt = 'ALTER TABLE ' + @tbl + ' ADD ' + CHAR(13);
;WITH [rsStructure] AS (
SELECT
[name]
, [system_type_name]
, [is_nullable]
FROM [sys].[dm_exec_describe_first_result_set](
@Sql
, @Params
, 0
)
)
SELECT
@AlterStmt += QUOTENAME([name]) + SPACE(1) + [system_type_name] + IIF([is_nullable] = 0, ' NOT NULL' , SPACE(0)) + ',' + CHAR(13)
FROM [rsStructure];
SET @AlterStmt = LEFT(@AlterStmt, LEN(@AlterStmt) - 2);
-- finally update table structure
EXEC [sys].[sp_executesql] @AlterStmt;
-- remove dummy column
SET @RemColStmt = 'ALTER TABLE ' + @tbl + ' DROP COLUMN ' + @DummyCol;
EXEC [sys].[sp_executesql] @RemColStmt;
-- populate table with @Sql statement data
IF @PopulateTable = 1
BEGIN
EXEC('INSERT INTO ' + @tbl + ' ' + @sql);
END;
END TRY
BEGIN CATCH
/* Use some error formatting sp instead */
SELECT @ErrCode = ERROR_NUMBER()
, @ErrMsg = ERROR_MESSAGE();
END CATCH
RETURN @ErrCode;
END
GO
GO
CREATE PROCEDURE [dbo].[spQueryResultAsHtmlTable]
@ErrCode INT OUT
, @ErrMsg NVARCHAR(4000) OUT
, @Sql NVARCHAR(MAX)
, @Params NVARCHAR(MAX)
, @HtmlTable NVARCHAR(MAX) OUT
AS
/* Makes Html table by result, returned by provided @Query
*/
BEGIN
SET NOCOUNT ON;
SET ARITHABORT ON;
BEGIN TRY
DECLARE @ERR_CODE_OK INT = 0
, @ERR_CODE_FAILED INT = 50000;
SET @ErrCode = @ERR_CODE_OK;
DECLARE @HtmlAsHml XML
, @ColumnList NVARCHAR(MAX) = SPACE(0);
IF NULLIF(LTRIM(RTRIM(@Sql)), SPACE(0)) IS NULL THROW @ERR_CODE_FAILED, 'Empty @Query received', 1;
IF OBJECT_ID('tempdb..#QueryResult') IS NOT NULL DROP TABLE [#QueryResult];
CREATE TABLE [#QueryResult] ([dummy_col] BIT);
EXEC [dbo].[spAlterTblByRs]
@ErrCode = @ErrCode OUT
, @ErrMsg = @ErrMsg OUT
, @Sql = @Sql
, @Params = @Params
, @Tbl = '#QueryResult'
, @DummyCol = 'dummy_col'
, @PopulateTable = 1;
IF @ErrCode <> 0 THROW @ErrCode, @ErrMsg, 1;
SELECT @ColumnList += IIF([column_ordinal] = 1, SPACE(0), ',') + '[td] = [' + [name] + ']'
FROM [sys].[dm_exec_describe_first_result_set](
@Sql /* @tsql */
, @Params /* @params */
, 0 /* @browse_information_mode */
)
ORDER BY [column_ordinal] ASC;
DECLARE @h XML
, @d XML;
/* Prepare headers */
;WITH [headers] AS (
SELECT [h] = CONVERT(XML, (SELECT
[th] = [name]
FROM [sys].[dm_exec_describe_first_result_set](
@Sql /* @tsql */
, @Params /* @params */
, 0 /* @browse_information_mode */
)
ORDER BY [column_ordinal] ASC
FOR XML PATH(''), ROOT('tr')))
)
SELECT @h = [h] FROM [headers];
/* Prepare rows */
SET @sql = N'
;WITH [data] AS (
SELECT [d] = (SELECT
' + @ColumnList + '
FROM [#QueryResult]
FOR XML RAW (''tr''), ELEMENTS XSINIL, TYPE)
)
SELECT @d = [d] FROM [data]';
SET @params = N'@d xml output';
EXECUTE [sp_executesql]
@stmt = @sql
, @params = @params
, @d = @d OUTPUT;
/* Make table html */
SET @HtmlAsHml = CONVERT(XML, (SELECT [*] = @h, [*] = @d FOR XML PATH('table')));
SET @HtmlAsHml.modify('insert attribute cellpadding {"2"} into (table)[1]')
SET @HtmlAsHml.modify('insert attribute cellspacing {"2"} into (table)[1]')
SET @HtmlAsHml.modify('insert attribute border {"1"} into (table)[1]')
/* Prepare value to be returned */
SET @HtmlTable = CONVERT(NVARCHAR(MAX), @HtmlAsHml);
END TRY
BEGIN CATCH
/* Use some error formatting sp instead */
SELECT @ErrCode = ERROR_NUMBER()
, @ErrMsg = ERROR_MESSAGE();
END CATCH;
RETURN @ErrCode;
END;
GO
/* Usage */
DECLARE
@ErrCode INT
, @ErrMsg NVARCHAR(4000)
, @Sql NVARCHAR(MAX) = 'select top (10) * from sys.tables'
, @HtmlTable NVARCHAR(MAX);
EXEC [dbo].[spQueryResultAsHtmlTable]
@ErrCode = @ErrCode OUT
, @ErrMsg = @ErrMsg OUT
, @Sql = @Sql
, @Params = NULL
, @HtmlTable = @HtmlTable OUT; /* YOur desired html table here */
IF @ErrCode <> 0 THROW @ErrCode, @ErrMsg, 1;
加载中,请稍侯......
精彩评论