Producing HTML from Transact-SQL

for RuBoard

Because of its basic simplicity, HTML isn't terribly hard to produce. That's why, to this day, many hard- core HTML coders prefer simple tools like Notepad to all-encompassing products like Frontpage. Compared with its forerunner SGML, HTML is very basic indeed. As such, it's easy to program and easy to produce. In fact, you can emit HTML from Transact-SQL with very little effort at all.

Tables

Tables represent a natural display format for relational data. Because HTML offers direct support for tabular data display, it only makes sense to explore producing HTML tables from SQL Server data. HTML tables are delimited by <TABLE> tags and each row is delimited with <TR> tags. Each piece of data within a table is delimited with <TD> tags. Given that these tags and the data they enclose consist of plain text, you can easily produce an HTML table using a simple Transact-SQL query. Listing 11-1 illustrates one way of doing this:

Listing 11-1 Transact-SQL that produces HTML.
 SET NOCOUNT ON USE Northwind GO SELECT '<TABLE BORDER="1">','','','' UNION ALL SELECT TOP 10 '<TR>','<TD>'+CompanyName+'</TD>','<TD>'+CustomerId+'</TD>','</TR>' FROM customers UNION ALL SELECT '</TABLE>','','','' 

(Results)

 ------------------ --------------------------------------------- ------------------- <TABLE BORDER="1"> <TR>           <TD>Alfreds Futterkiste</TD>                  <TD>ALFKI</TD> </TR> <TR>           <TD>Ana Trujillo Emparedados y helados</TD>   <TD>ANATR</TD> </TR> <TR>           <TD>Antonio Moreno Taquera</TD>              <TD>ANTON</TD> </TR> <TR>           <TD>Around the Horn</TD>                      <TD>AROUT</TD> </TR> <TR>           <TD>Berglunds snabbkp</TD>                   <TD>BERGS</TD> </TR> <TR>           <TD>Blauer See Delikatessen</TD>              <TD>BLAUS</TD> </TR> <TR>           <TD>Blondesddsl pre et fils</TD>             <TD>BLONP</TD> </TR> <TR>           <TD>Blido Comidas preparadas</TD>            <TD>BOLID</TD> </TR> <TR>           <TD>Bon app'</TD>                             <TD>BONAP</TD> </TR> <TR>           <TD>Bottom-Dollar Markets</TD>                <TD>BOTTM</TD> </TR> </TABLE> 

Like most kinds of tables, HTML tables have three basic parts : the header, the body, and the footer. This query produces all three through the use of UNIONs. The first SELECT produces the headerthe obligatory <TABLE> tag, along with a width specification for the lines that make up the table grid. The second SELECT produces the body of the table, enveloping each column value in the required <TD> tags and surrounding each row with <TR> tags. The final SELECT produces the closing </TABLE> tag.

If you run this query in OSQL with column headers and line numbering disabled, it will produce an HTML table that can then be displayed in a browser. Figure 11-1 illustrates what it should look like:

Figure 11-1. The table produced by our Transact-SQL.

graphics/11fig01.gif

Given that the script does all the real work of producing the HTML, it's trivial to generalize the call to OSQL by placing it in a .CMD file. Listing 11-2 illustrates a .CMD file that you can use to create and open an HTML document produced by OSQL:

Listing 11-2 TSQL2HTML.CMDa command file that produces HTML from T-SQL.
 @echo off OSQL -E -h-1 -n -i%1.sql -o%1.html %2 %1.html 

TSQL2HTML.CMD takes two parameters: the name of the script to run and an optional OSQL command-line parameter (for example, to specify the server name , as in -Sservername). If the script you pass in renders valid HTML, it will be displayed once it's been written to an .HTML file.

Column Headings

You may have noticed that our HTML table contains no column headings. This is easily remedied. Here's a variation on the earlier query that includes column headings (Listing 11-3):

Listing 11-3 A revision of the Customers query that includes column headings.
 SET NOCOUNT ON USE Northwind GO SELECT '<TABLE BORDER="1">','<TH>Company Name</TH>','<TH>Customer ID</TH>','' UNION ALL SELECT TOP 10 '<TR>','<TD>'+CompanyName+'</TD>','<TD>'+CustomerId+'</TD>','</TR>' FROM customers UNION ALL SELECT '</TABLE>','','','' 

(Results)

 ------------------ ------------------------------------------- ---------------- ---- <TABLE BORDER="1"> <TH>Company Name</TH>                       <TH>CustomerID</TH> <TR>               <TD>Alfreds Futterkiste</TD>                <TD>ALFKI</TD>   </TR> <TR>               <TD>Ana Trujillo Emparedados y helados</TD> <TD>ANATR</TD>   </TR> <TR>               <TD>Antonio Moreno Taquera</TD>            <TD>ANTON</TD>   </TR> <TR>               <TD>Around the Horn</TD>                    <TD>AROUT</TD>   </TR> <TR>               <TD>Berglunds snabbkp</TD>                 <TD>BERGS</TD>   </TR> <TR>               <TD>Blauer See Delikatessen</TD>            <TD>BLAUS</TD>   </TR> <TR>               <TD>Blondesddsl pre et fils</TD>           <TD>BLONP</TD>   </TR> <TR>               <TD>Blido Comidas preparadas</TD>          <TD>BOLID</TD>   </TR> <TR>               <TD>Bon app'</TD>                           <TD>BONAP</TD>   </TR> <TR>               <TD>Bottom-Dollar Markets</TD>              <TD>BOTTM</TD>   </TR> </TABLE> 

The only real change here is the addition of <TH> tags and column heading text to the first SELECT statement. These appear in the first row of output from the query. HTML <TH> tags specify headings for table columns , so if you save this result set to an .HTML file and open it in a browser, you should see something like the table in Figure 11-2.

Figure 11-2. The table with column headings.

graphics/11fig02.gif

Of course, you can produce more than just HTML tables using this technique. You can produce any valid HTML you want, and you can use the SQL Server query processor to help you iterate through the data as you do.

for RuBoard


The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
ISBN: 201700468
EAN: N/A
Year: 2005
Pages: 223

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net