Asset Management Suite

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

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 

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

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 

 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.  

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 

Summary