Semi-dynamic SQL

Taking our SQL correlation code examples shown in the code samples pages one stage further: now we have got SQL Server to give us the latest job status, how about having the results automatically posted to the web every time there is a change?

Luckily, this is very easy using the SQL Server Web Assistant. The Web Assistant creates a trigger on the table you choose to export to the web. By default, the Web Assistant creates three separate triggers, one for each event -- insert, delete or update. You can combine these into one trigger that fires for any event. And instead of using a table or a query as the Web Assistant basis, why not use a stored procedure which is pre-compiled and more efficient?

Our final optimised status query was:

select
  jobid AS ID
, jobstatus AS "STATUS"
, convert(varchar(20), statusdate, 113) AS "WHEN"
, TECH
from jobs
order by jobid

Create a stored procedure for the web-task to use

We can create a slightly modified stored procedure "AllStatus" from this query:

create proc AllStatus
AS
select
  jobid AS JOB
, convert(varchar(20),statusdate,113) AS "STATUS LAST CHANGED"
, CUSTOMER
, TECH
, jobstatus "STATUS"
from jobs
order by jobid

Create a single trigger to handle all change types: insert, update, delete

Now using the Web Assistant and a little editing on the SQL Server triggers it creates, we create a single trigger on the Jobs table for the web page generation process:

CREATE TRIGGER WebJobs ON dbo.jobs FOR INSERT, DELETE, UPDATE AS
begin
execsp_makewebtask
@outputfile= '[mywebname]\jobstatus.htm', @query='exec dbo.AllStatus',
@query='exec dbo.AllStatus',
@fixedfont=0, @bold=0, @italic=0,
@colheaders=1,
@lastupdated=1, @HTMLheader=1,
@username=<'username'>, @dbname=<'dbname'>,
@templatefile='[mywebname]\template.htm',
@webpagetitle='JobStatus', @resultstitle= 'JobStatus',
@maketask=0, @rowcnt=0
end
GO

Use a template file to get the results, then include that page in another page

This will now output standard HTML for any browser using standard table formats (<TABLE>, <TR> and <TD> tags). Note that we use a template file ("@templatefile= ...") with an <%insert_data_here%> tag to ensure consistency of style. Note also the use of the stored procedure ("@query...").

With a template file, all you need is the "<%insert_data_here%> statement in one place where the table is to go. For finer control, you can also use the same syntax slightly differently:

<TABLE BORDER>
<TR>
  <TH>JOB</TH>
  <TH>STATUS LAST CHANGED</TH>
  <TH>CUSTOMER</TH>
  <TH>TECHIE</TH> 
</TR>
<%begindetail%>
<TR>
  <TD><%insert_data_here%></TD>
  <TD><%insert_data_here%></TD>

  <TD><%insert_data_here%></TD>
  <TD><%insert_data_here%></TD> 
</TR>
<%enddetail%>
</TABLE>

So here are our DYNAMIC results (yes they are different from the previous code samples, because changes have been made!):

Copyright DBU 2004.
Last updated 24 May 2004

Data Base Unlimited
The Old School
Winchfield
Hook RG27 8DB
England

Phone:
+44 (0)1256 393050
Telefax:
+44 (0)1256 393051

Information: info-nospam@dbu.co.uk
Jobs information
Jobs mail: dbuapplicants-nospam@dbu.co.uk
Webmaster: webmaster-nospam@dbu.co.uk