Transform any Query to Html in T-Sql

A new management function in Sql Server 2012 can extract metadata from arbitrary queries. Paired with dynamic sql this allows to transform (almost) any query into a nice looking html table. This is particularly interesting if you want to send query results with Sql Server Database Mail.

 I recently had a customer requesting to receive query results periodically per e-mail. Not having Sql Server Reporting Services available there are two solution paths to this problem:

  1. Create an excel file from the query and send as attachment
  2. Output the query to html and include it in the E-Mail body

Option 1 (sending data as attachment) would involve rather complicated clr-integrated programming and server-side generation of excel files. This would only be appropriate for larger data volumes. Also the implementation would take considerably more time and effort than option 2 (send as html).

This article describes a full solution to option 2. You can download the code file from the link at the end.

Sample data and output

To demonstrate the solution I made up some sample data – fictitious statistics of authoring activities on an international content platform.

vorher-nachher

I wanted to have the tabular query output formatted nicely and  some text included before and after the table. 

So, let’s produce a table with sample data:

 

 

1. Extract query metadata

Before Sql Server 2012 you could use INFORMATION_SCHEMA to get metadata of database objects, but there was no way to extract metadata from arbitrary queries – this has changed. Sql Server now offers a dmv-query (dynamic management view)  which takes a query statement and returns metadata about this query – without actually executing it.  sys.dm_exec_describe_first_result_set  operates on Tables, Views, Stored Procedures or Table-valued Functions. The full syntax is:

 where @tsql is the query to execute, @params provides required parameters to the query (NULL if none) and @include_browse_information would have the query executed with the FOR BROWSE – option.

To get a list of field names and types, we call:

This returns self-explaining metadata about our query, which we can use to transform row data and produce table headers  and a customized stylesheet.

sys.dm_exec_describe_first_result_set_results

Templating the html output

As a first step in our automated html conversion we have to agree on the general layout of the html code generated. As I said before, I want to have the output nicely formatted with optional text before and after the table. So the general layout would be:

 The placeholders are in handlebars-notation only incidentally, so everybody knows what’s meant here. They will be replaced by generated text blocks later.

Produce the html table header from metadata

As a first exercise we can now generate the html to replace the {{hdrRows}} – placeholder directly from the metadata.

 producing the following html in @hdrRows:

 (line breaks and indents for readability only)

 

2. Extract row data using dynamic sql and metadata

Now we are ready to extract row data and output it as html. Since we want to operate on standard queries (Select … From … ) and on Stored Procedures, we need to capture query results in a table variable to be processed. This can only be done with Dynamic SQL, like:

Above code is the SQL, which has to wrapped and executed as Dynamic SQL – the generator code is somewhat different, of course (see next code snippet). Here we produce the html for each table row based on the data in the sql server table’s records.

Each column is html-encoded (and converted to NVARCHAR by f_htmlEncode).  For more information about html-encoding in T-Sql, see my article How to html-encode special characters in T-Sql. Apostrophes are removed. Results are concatenated and assigned to @html, which acts as OUTPUT variable.

And this is the full code to build these sql statements dynamically.

At the end we execute the generated sql statement dynamically and insert results into the html template. 

 

3. Create Stylesheet and Finalize Helper Proc

So, content is ready. This leaves us with the final task to format output properly. We do it with an inline stylesheet, which is placed in the head-section. Most of it is static – you may adapt it to your needs. But text-alignment and cell-widths depend on the field type, so we need to generate some styles from query metadata.

This is the final stylesheet:

 The last style is applied to all numeric table columns. Please look up the generator code in the download.

 

4. Usage with database mail

The steps above are wrapped up in a single stored procedure, named p_ConvertAnyQueryToHtml. The calling syntax is:

We call it, get the resulting string, attach it as body and send.

Remember, that the actual display of results depends on the capabilities of your e-mail client. 

Phew! Long post. 

 

And here is the code download


References / Notes: