Let's take a look at the template file, the template template, the suffix name is tpl,tpl file and HTML file, in the TPL file note the "> tag, respectively, representing the meaning of the following:
BeginDetail: Represents the beginning of a preparation to begin replacing a template file
EndDetail: Represents the end replacement template file
Insert_data_here: Represents the data that indicates where to insert the result set. If the result set record contains more than one field, Insert_data_here inserts the data sequentially in the order in which it is in the record, that is, in the query statement select to execute the order of the fields in the statement. That is, each field in the result record can only be inserted once in the page. If you want to use a field more than once in a page, you can assign it to a variable first. You can then use this variable again and again.
So we're going to do a simple format of the TPL template, named Outputhtml.tpl, the template's HTML code is as follows:
<meta http-equiv= "Content-type" content= "text/html; charset=gb2312"/>
<title>show HTML</TITLE
<body>
<center>
<p>
<table border= "1" cellpadding= "0" cellspacing= "0"
<TR> <TH><B> company name </b></th>
<TH><B> Contact </b ></TH>
<TH><B> product name </b></th>
<TH><B> Product name </b></th
</tr>
<%begindetail%>
<TR> <TD> <%insert_data_here%> </td>
<TD align=right><%insert_data_here%></td>
<td align=right><%insert_data_here%> </td>
<td align=right><%insert_data_here%></td>
</tr>
<%enddetail%
</table>
</center>
</body>
Class=msonormal style= "text-indent:21pt" > template file, we first store it under the "D:sql Server Files" directory, and then we execute the following code in SQL Server Query Analyzer:
Executesp_makewebtask
@outputfile = ' d:sqlserver file outputhtml.htm ',
@query = ' Selectsuppliers.companyname,suppliers.contactname,products.productname,products.unitprice
Fromsuppliersinnerjoinproductsonsuppliers.supplierid=products.supplierid
Wheresuppliers.supplierid=1 ',
@templatefile = ' d:sqlserver file Outputhtml.tpl ',
@dbname = ' NorthWind ',
@rowcnt = 0,
@whentype = 9,
@lastupdated =1
Go
Let's take a look at this SQL Server's sp_makewebtask system stored procedure, which is used to create a task that generates an HTML document that contains the data returned by the query that was executed. In this case, the above parameters represent, respectively, the meaning:
@outputfile
The location where the HTML file is generated. If the document is to be generated on a remote computer, the parameter can be a UNC name.
@query
The query to run. When you run a task, the results of the query are displayed in a tabular format in the HTML document. You can specify multiple SELECT queries so that multiple tables can be displayed in OutputFile.
@templatefile
The path to the template file used to generate the HTML document. The template file contains the formatting feature information and markup for the HTML document, which indicates where the query results are added to the HTML table.
@dbname
The name of the database on which to run the query.
Specifies whether the query result is displayed in bold (1) or not bold (0). The bold data type is tinyint and the default value is 0.
@rowcnt
Specifies the maximum value of the rows displayed in the generated HTML document. The default value is 0, which is used to specify all rows that are displayed in the HTML document that satisfy the given query.
@whentype
Specifies when to run the task of creating an HTML document. 9 represents the creation of a page immediately and upon request. The HTML document will be created and re-created immediately.
@lastupdated
Specifies whether the generated HTML document displays the last updated time: timestamp indicates the date and time (1) of the last update or no timestamp (0). In an HTML document, the timestamp is the previous row of the query's results.
The above stored procedure, if executed under SQL Server 2005, must be opened to execute it, and the following code can be executed:
sp_configure ' showadvancedoptions ', 1;
Go
Reconfigure;
Go
sp_configure ' webassistantprocedures ', 1;
Go
Reconfigure
Go
After the execution, we look at the D disk SQL Server file to generate a outputhtml.htm file, view the original code, you can see the template tag to replace the corresponding database content, the code is as follows:
<HEAD>
<HEAD>
<meta http-equiv= "Content-type" content= text/html; charset=gb2312 "/>
<title>show html</title>
<body>
<center>
<P>
<table border= "1" cellpadding= "0" cellspacing= "0"
<TR> <TH><B> Company Name </b></th>
<TH><B> contact </b></th>
<TH><B> Product name </b></ TH>
<TH><B> Product name </b></th>
</tr>
<TR> <TD> Exotic Liquids < /td>
<td align=right>charlotte cooper</td>
<td align=right>chai</td>
<TD Align=right>18.0000</td>
</tr>
<TR> <TD> Exotic liquids </td>
<td Align=right>charlotte cooper</td>
<td align=right>chang</td>
<TD ALIGN=RIGHT> 19.0000</td>
</TR>
<TR> <TD> Exotic Liquids </TD>
<TD Align=right>charlotte cooper</td>
<TD Align=right>aniseed syrup</td>
<TD align=right>10.0000</td>
</TR>
</TABLE>
</center>
</H2>
</BODY>
</HTML>
Run this HTML file to see the results as shown in figure:
Figure SQL Server query results output to HTML page
It corresponds to the result of the query with the SQL statement, so that we enter the query statement into the HTML page.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
