paschott/QueryToHtmlTable.sql

## QueryToHtmlTable.sql
-- 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.
-- Adapted and added code to format the code
-- =============================================
CREATE OR ALTER 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, '''', '''''');


  /* Can adjust the @Header values below  to change table formatting. */
  DECLARE @realQuery nvarchar(MAX) = '
    DECLARE @Header nvarchar(1000) =
N''<html><head><style>  table, th, td {
border: 1px solid black;      border-collapse: collapse;  }
th {background-color: #003399;color:white;}
th, td {      padding: 5px;      text-align: left;  }
tr:nth-child(odd) {    background-color: #ccddff;  }
</style></head><body>''

	DECLARE @Footer nvarchar(100) = N''</body></html>''

    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 = @Header + ''<table border="1">'' + @headerRow + @html + ''</table>'' + @Footer;
    ';

  EXEC sys.sp_executesql @realQuery, N'@html nvarchar(MAX) OUTPUT', @html=@html OUTPUT
END
GO


DECLARE @html nvarchar(MAX);
EXEC ##QueryToHtmlTable @html = @html OUTPUT
, @query = N'SELECT top(10) PersonType,NameStyle,Title,FirstName,MiddleName,LastName,Suffix FROM Person.Person'
, @orderBy = N'ORDER BY LastName,FirstName desc';

--select @html /* Test output - can preview at a site like this: https://codebeautify.org/htmlviewer */


/*
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;

*/
	-- 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.
	-- Adapted and added code to format the code
	-- =============================================
	CREATE OR ALTER 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, '''', '''''');


	/* Can adjust the @Header values below to change table formatting. */
	DECLARE @realQuery nvarchar(MAX) = '
	DECLARE @Header nvarchar(1000) =
	N''<html><head><style> table, th, td {
	border: 1px solid black; border-collapse: collapse; }
	th {background-color: #003399;color:white;}
	th, td { padding: 5px; text-align: left; }
	tr:nth-child(odd) { background-color: #ccddff; }
	</style></head><body>''

	DECLARE @Footer nvarchar(100) = N''</body></html>''

	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 = @Header + ''<table border="1">'' + @headerRow + @html + ''</table>'' + @Footer;
	';

	EXEC sys.sp_executesql @realQuery, N'@html nvarchar(MAX) OUTPUT', @html=@html OUTPUT
	END
	GO


	DECLARE @html nvarchar(MAX);
	EXEC ##QueryToHtmlTable @html = @html OUTPUT
	, @query = N'SELECT top(10) PersonType,NameStyle,Title,FirstName,MiddleName,LastName,Suffix FROM Person.Person'
	, @orderBy = N'ORDER BY LastName,FirstName desc';

	--select @html /* Test output - can preview at a site like this: https://codebeautify.org/htmlviewer */


	/*
	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;

	*/