Video Screencast Help
Protect Your POS Environment Against Retail Data Breaches. Learn More.

SQL Stored Procedure to Convert SQL Table Into HTML (v1.0)

Created: 19 Sep 2011
ianatkin's picture
0 0 Votes
Login to vote

When creating complex email reports, one thing you can find yourself repeatedly doing is munging SQL SELECT statements into a format suitable for HTML presentation. Today's download contains a SQL stored procedure which simplifies reporting by enabling you to print SQL tables as HTML strings.

Introduction

Over the last few weeks I've been looking a lot at my Altiris reports.Many of the reports were frankly getting a bit long in the tooth, and to be honest they weren't that useful by themselves. You see the recipients often had to click through to the Altiris console to get to the data they really wanted. In some cases this was fine, but sometimes you don't want to give people console access just to provide them with information you really should have given them in the first place.
 
So I thought it a good time to formulate a new template for my summary reports. In particular, I wanted in the new report the summary to be first of two sections. I wanted to have as a second section all the details that they'd probably be curious about after reading the leading summary.
 
Now, although Altiris make's it incredibly simple to email the results of a select query, should you want to build up a more complex email report containing multiple tables you can become unstuck. At this point you have to wrestle with HTML formatting so you can merge all your HTML-ified table data into the single SELECT that Altiris understands. I knew that Altiris has some code somewhere for doing this (as in Helpdesk 6.x you can email notifications containing the results of a query) but I failed to locate the magic code that made that work. So, I started work on creating my own procedure to HTML-ify my tables.
 
Once I started implementing this stored procedure, I actually saw an awful lot of reporting opportunities open up. Within a few days I'd updated over a dozen summary reports to include extended details all invoked from a single email notification. I found this instantly most useful for increasing the quality of our helpdesk worker reports, our anti-virus stats and our notification rules which perform routine maintenance actions.
 
In addition to providing the SQL in this download, I've detailed as much as is practical about how this stored procedure works. This will provide a little 'added value' for those who are interested in learning a bit more T-SQL. And there's lot's of juicy stuff in here too; on top of introducing SQL Stored procedures we'll be take a peek at temporary tables, scoping, cursors and dynamic SQL too. All very interesting stuff, and applicable to anyone wanting to leverage their MS SQL Server that little bit more.
 

How To Use spCustomTable2HTML

The attached spCustomTable2HTML stored procedure allows you to build up quite complex HTML within your T-SQL so that you can dump it all out at the end with a single SELECT.   To start using this stored procedure, download the attached zip file spCustomTable2HTML.zip, extract from this the .sql file and copy the contents in to SQL Management Studio and execute it.

.

After execution, you should see the message "Command Completed Successfully" as illustrated above. This means the stored procedure has now been loaded into your Altiris database (if you want the procedure to be loaded into a different db, just change the USE  line at the top of the script).

To show how to use this procedure, let's test it out with an example. Paste the following completely mission-critical query into a SQL Server Management query window,

 Create table Buffy (
Season int,
episode_number int,
episode_name nvarchar(100)
)

insert into Buffy VALUES (1,1,'Welcome to the Hellmouth')
insert into Buffy VALUES (1,2,'The Harvest')
insert into Buffy VALUES (1,3,'Witch')
insert into Buffy VALUES (1,4,'Teacher''s Pet')
insert into Buffy VALUES (1,5,'Never Kill a Boy on the First Date')
insert into Buffy VALUES (1,6,'The Pack')
insert into Buffy VALUES (1,7,'I, Robot... You, Jane')
insert into Buffy VALUES (1,8,'Nightmares')
insert into Buffy VALUES (1,9,'Out of Mind, Out of Sight')
insert into Buffy VALUES (1,10,'Prophecy Girl')

select * from Buffy 
 
So what we've done is create a table in our database and then populated it with lots of useful data. If you've added the Buffy table into your production Altiris database, you might want to cautiously investigate the T-SQL DROP TABLE command when you've finished with this article. Moving on, the result of the above SQL on execution should be,
 
 

To get the above in the equivalent HTML, we can execute the following T-SQL query,

 DECLARE @HTML NVARCHAR(MAX)

EXEC SpCustomTable2HTML 'Buffy', @HTML OUTPUT

SELECT @HTML 

 

To which the result is,

   <table>
    <tr><td>Season</td><td>episode_number</td><td>episode_name</td></tr>
    <tr><td>1</td><td>1</td><td>Welcome to the Hellmouth</td></tr>
    <tr><td>1</td><td>2</td><td>The Harvest</td></tr>
    <tr><td>1</td><td>3</td><td>Witch</td></tr>
    <tr><td>1</td><td>4</td><td>Teacher's Pet</td></tr>
    <tr><td>1</td><td>5</td><td>Never Kill a Boy on the First Date</td></tr>
    <tr><td>1</td><td>6</td><td>The Pack</td></tr>
    <tr><td>1</td><td>7</td><td>I, Robot... You, Jane</td></tr>
    <tr><td>1</td><td>8</td><td>Nightmares</td></tr>
    <tr><td>1</td><td>9</td><td>Out of Mind, Out of Sight</td></tr>
    <tr><td>1</td><td>10</td><td>Prophecy Girl</td></tr>
  </table>

 

If you want to add some styles, then the function also optionally supports the addition of user agents into the table and header row tags;

  EXEC SpCustomTable2HTML         
        'Buffy',         
        @HTML OUTPUT
        'style="font:8pt" class="AltListBorder" cellpadding="2" cellspacing="1"'
        'class="RowHeader"' 
 
To see how this looks, let's see how both the unstyled and styled results look when rendered in a browser,

Not bad, and pretty simple too!

 

How The spCustomTable2HTML Stored Procedure Works

My aim for this download was to create a re-usable block of code which could be called to HTMLify a table. The coding task is straightforward -we want to be able to take an arbitrary table as an input, and the output should be a stream of the equivalent HTML.

SQL provides two programming constructs which allow us to abstract blocks of code from our main routines, permitting them to be called through the use of simpler statements. These constructs are the User Defined Functions (UDFs) and stored procedures (SPs). UDFs however have two drawbacks which will affect us in this project -they can't perform INSERTs, UPDATEs or DELETEs on temporary data and further they can't leverage dynamic SQL. This means that for this particular project we 'll be looking at creating a stored procedure.

So, let's take a look at our SP input -the target table we want to HTMLify. Having decided the procedure needs to accept the target table as a parameter, it was time to figure out how to achieve this. Which is where my problems started. You see, SQL Server versions 2005 and below do not support the passing of tables as parameters. As I need the SP to be portable across my 2005/2008 SQL Server estate that means I can't rely on table-valued parameters being supported. This leaves me with the question of if I can't pass the table itself as an argument, how can I let the SP know which table needs HTMLifying?

The only option here I could think of was to pass to the SP the table's name as a parameter instead. This is considered on the whole to be bad practice (I think because it necessitates the use of dynamic SQL) but as I can't see a practical alternative, passing the table's name will have to do for now. 

 

First Question -Do we Create A Copy of the Target Table?

Now let's leap forward a bit in the process. From within the SP I'll be using SELECT statements to query my target table, but we now have to deal with the complication that our table's name is not known until execution time. Traditional SQL statements will not allow this -the table name can't be presented as a variable in the SELECT statement. So we have to implement a little trick to delay the evaluation of the SELECT statements until execution time. This is so that the table name variable can be expanded to complete the SELECT statement properly.  This is achieved using  Dynamic SQL, which enables us to store the SQL statement as a string which we expanded  before execution.

Having determined that we'll have to using Dynamic SQL to explore our target table, we have to accept a little complication. Dymanic SQL scoping rules state that any temporary objects we create within dynamic sql are treated as a children of the dynamic query. This means that they will simply be cleaned up when the dynamic query completes. Creating a temporary copy of our input table using Dynamic SQL is therefore pointless as once the table copy is complete and the dynamic query ends it will simply be flushed away. Not useful.

Three options immediately leap to attention at this point,

  1. Use Global Temporary Variables
    These tables are more persistent than the traditional temporary tables. Whereas temporary tables are denoted with a single hash prefix to their name, global tables are identified with a double-hash prefix. Global tables take their name from the fact that they are visible throughout all server sessions until the creating session ends. This means, for me, that I must take care when copying tables to a global temporary table as this can result in name clashes with other executing instances of the procedure .
     
  2. Perform All Required SQL Operations within a Single Dynamic SQL Statement
    Instead of exiting the Dymanic SQL statement after copying the target table to a temporary table, we could go the 'whole hog' and do ALL the required processing using a single dynamic SQL statement. The drawback here is that the longer the code is in the SQL string passed for dynamic execution,  the more tricky that code will be to debug as basic syntax errors will only manifest at execution time. Debug helpers (PRINT statements scattered a key points throughout the code) will be key here to getting the code up and running quickly. 
     
  3. Do not Copy the Table
    We could of course have the stored procedure work on the input table's contents directly. This gets around our scoping problem nicely -the table being targeted for processing will obviously already be around before the stored procedure is called (and any subsequently OK for any dynamic SQL too).  A fly in the ointment here is that we'll have to perform some column add and delete operations on the target table to process it for indexing purposes, and that's potentially harmful. In my use case scenarios I'll only be working on temporary tables though, and any changes I make will be undone for thoroughness. Although this looks to be a simpler approach,  it does not sit well.

Each of the above routes have pros and cons, and I had trouble deciding which was the best way to go. So, rather than hang around,  I decided to implement all three variations. I ploughed on and created functioning stored stored procedures based all all three of the decision forks above. All of them were fun to write, and each presented different challenges. For example, using a Global Temporary variable meant I ended up using the @@SPID variable to ensure uniqueness between concurrent sessions. Also, the final route of not copying the target table meant that I had to add an indexing column to the original table, and then remove it afterwords. Not as simple as I first thought when the column has a constraint and which takes some thinking to remove should the table be temporary.

The one I like best (and present here) is the second option, where the stored procedure which is executed almost entirely in a single string that represents a nested T-SQL dynamic query. I can't help but think I've made all this more complex than it needs to be... so if you've any ideas on how to slim the code down please let me know!

Pros of using the entirely dynamic SQL approach were,

  1. Can HTMLify both permanent and temporary tables (as target table is always copied to a temporary table of a known name)
  2. No risk to target table as operations are only actioned on the temporary copy

Cons of this approach were,

  1. The Dynamic SQL was an absolute devil to debug

So, to answer the question presented in the title of this section -yes, I decided that the best approach was to copy the target table. Although it was easier to work directly with the target table, it went against programming intuition to work that way.  Far better to do a little extra work and do it nicely. And for that approach to work well, nested dynamic SQL seemed to be the answer.

 

How Dynamic SQL Works

Dynamic SQL is useful for those situations where critical objects in your SQL queries are just not known at design time. For example here we don't know the table name that the user is going to want HTMLified.  This means our SQL statements have to be crafted at execution time.

Let's take a trivial example of Dynamic SQL. Assuming you've already created the Buffy table, let's try asking Buffy's contents by referring to the table as a variable,

 DECLARE @Table nvarchar(100)

SET @Table='Buffy'
SELECT * from @Table 
 
The result of this isn't the contents of the table Buffy however. Rather, we get the error 'Must declare the table variable "@Table" '. This error indicates that the SQL engine in interpreting the variable @Table as being a real table, i.e. a pointer to a table structure. In fact it's just a pointer to a table's name (a different thing entirely)
 
To get SQL to take the value of our scalar variable and present it to the engine as 'SELECT * FROM Buffy' the trick is to store the select statement in a string which is evaluated and executed at runtime. For execution, we use the EXEC() command,
 
 DECLARE @Table nvarchar(100)
DECLARE @sql NVARCHAR(MAX)

SET  @Table='Buffy'
SET  @sql='SELECT * FROM' + @Table
EXEC (@sql)

Here you can hopefully see that the scalar variable @sql will be expanded at runtime into 'SELECT * FROM Buffy' and then passed to EXEC for execution.

That is a simple case of Dynamic SQL, but we actually wanting something a bit more complex. We want EXEC to pass data back to the us. This functionality of passing parameters in and out of EXEC requires us to enhance EXEC with a built-in stored procedure called sp_executesql. This will accept a parameter block as an argument to allow us to state the parameters we with to pass in and out of the stored procedure.

The syntax for sp_executesql is as follows,

EXEC sp_executesql @sql, 
                   @parameters,
                   @param1=value1, @param2=value2 ...

where,

  • @sql holds the SQL statement we're passing into EXEC for execution
  • @parameters contains the parameter definitions we require to be passed into @sql
  • the remainder paramter/value pairs detail the parameter values. 

In short, the dynamic SQL is going to be executed in it's own scope so we have to make sure everything we want to get in and out of the SP is declared and set in the EXEC sp_executesql arguments. 

Taking again the example of the Buffy table, let's see how we would ask the SP to return an episide title from an input of a specific episode number

 DECLARE @Title NVARCHAR(500)
DECLARE @Table NVARCHAR(100)

DECLARE @Episode_Number INT
DECLARE @exec_sql NVARCHAR(MAX)

SET @Table='Buffy'
SET @Episode_Number=5

SET @exec_sql='SET @TitleOUT = (SELECT Episode_Name
FROM ' + @Table + '
WHERE Episode_Number= Cast(@ep_IN as nvarchar)) '

PRINT @exec_sql

EXEC sp_executesql @exec_sql,
N'@TitleOUT NVARCHAR(500) OUTPUT,@ep_IN INT',
@TitleOUT=@TITLE OUTPUT,
@ep_IN=@Episode_Number

SELECT @Title 
 

In the above  I've declared the output parameter as @TitleOUT which allows us to pass the title discovered by the select back out of EXEC.  I've also declared an input parameter of @ep_IN, an integer, to allow us to pass the episode number in.  

From the parameter pairs that follow the parameter declaration string, we can see that @TitleOUT is sent to the parameter @TITLE and @ep_IN which is used in the SP is set as the value of @Episode_Number. It's not the simplest topic in the world I admit. But you do get used to it!
 
Note: the OUT/IN postfixes to the variables are to denoted for clarity which variables are being passed in and out of the stored procedure.
 
 

Column Iteration

Next we come on to column iteration. If we copy out input table into the temporary table #MyTempTable for example, how do we identify it's column names? The answer to that is actually quite simple -they're stored in the temp database in the sys.columns table. To iterate through each column I've used the cursor of the form,

 DECLARE col CURSOR FOR
SELECT name FROM tempdb.sys.Columns
WHERE object_id=object_id('tempdb..#MyTempTable')
ORDER BY column_id ASC

This cursor ensures that the each column name of the example temporary table #MyTempTable  are iterated through in sequence. In order to start processing using our cursor, we first need to 'open' it with the command,

 OPEN col 

And then in order to take the first result of the SELECT statement in the cursor we execute a fetch,   

 FETCH NEXT FROM col INTO @ColumnName 

which in this case stores the result into the SQL variable @ColumnName. 

 

Finding the Nth Row of a Table

In order to create my HTML, I need to scan a row, grabbing one column at a time. To iterate through each row, I use a counter to loop from 1 to MAXROWS and grab all the column values for the row in question. I made this process simple by adding an extra index-like column to the target SQL table when I copied it to a temporary table,

 

select CustColHTML_ID=0,* INTO #CustomTable2HTML FROM + MyTable

--Now alter the table to add the auto-incrementing index. This will facilitate row finding
DECLARE @COUNTER INT
SET @COUNTER=0
UPDATE #CustomTable2HTML SET @COUNTER = CustColHTML_ID=@COUNTER+1 
 
This is quite neat. The first lines selects all the contents of MyTable and copies them to the temporary table #CustomTable2HTML adding the column CustColHTML_ID to it and filling it with zeros. Next we simply update the newly added column CustColHTML_ID, ensuring the values count nicely from 1 to the maximum number of rows in the table.
 
 

Summary

Today's download details how to produce a HTML table equivalent of a SQL table using a custom stored procedure. It was fun to write, and taught me a few things about SQL along the way. For those who'd like to extend their SQL knowledge, I've also documented my thinking and some sample code to explain the basic concepts behind the programming constructs I've used. More detailed documentation is however included in the code itself.
 
As usual, all comments appreciated. And if you know of an easier way -please let me know!
 
Kind Regards,
Ian./