لطفا منتظر باشید ...
Producing HTML from Transact-SQL
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>AnaTrujilloEmparedadosy helados</TD> <TD>ANATR</TD> </TR>
<TR> <TD>Antonio Moreno Taquería</TD> <TD>ANTON</TD> </TR>
<TR> <TD>Around the Horn</TD> <TD>AROUT</TD> </TR>
<TR> <TD>Berglunds snabbköp</TD> <TD>BERGS</TD> </TR>
<TR> <TD>Blauer See Delikatessen</TD> <TD>BLAUS</TD> </TR>
<TR> <TD>Blondesddsl père et fils</TD> <TD>BLONP</TD> </TR>
<TR> <TD>Bólido 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 .
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%1l %2
%1l
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 Taquería</TD> <TD>ANTON</TD> </TR>
<TR> <TD>Around the Horn</TD> <TD>AROUT</TD> </TR>
<TR> <TD>Berglunds snabbköp</TD> <TD>BERGS</TD> </TR>
<TR> <TD>Blauer See Delikatessen</TD> <TD>BLAUS</TD> </TR>
<TR> <TD>Blondesddsl père et fils</TD> <TD>BLONP</TD> </TR>
<TR> <TD>Bólido 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 .
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.
