Figure 1: Setting up the stored procedure for ColdFusion in Dreamweaver
A stored procedure gives you many benefits when building applications, including web applications using ColdFusion, ASP, and other technologies. They are fast because they are precompiled and they are secure because the database tables are not being accessed directly. One thing that is a little tricky in working with stored procedures is building dynamic queries. You can build a SQL statement as a string within a stored procedure and execute it using the EXEC statement (or xp_executeSQL), but this also defeats one of the purposes of building your functionality into a stored procedure: the statement is not compiled when it is built dynamically. This article shows one way of creating dynamic SQL on the fly while still allowing the stored procedure to be precompiled. I'll show the code in ASP and ColdFusion, but the techniques can be used in any language using a database that supports stored procedures (such as SQL Server).
This article assumes you are familiar with SQL and stored procedures. For a primer on stored procs, see Ray West's 3 part series:
Moving to Stored Procedures – An Introduction
Moving to Stored Procedures - Part 2
Moving to Stored Procedures - Part 3
and also Jay Oliver's tutorial:
The article will also use basic Dreamweaver server behaviors and application objects, so I will assume you are familiar with those, although Dreamweaver is not a necessary ingredient for this functionality.
In a web application, there are many times when you need to present information in a table -- such as in a search results query. The table is typically displayed with sortable column headings. Using the Northwind database for the example, suppose we wanted to select the ProductName and the UnitPrice from the Products table. The two fields will be displayed in a table with clickable column headings. Behind the scenes, when a column heading is clicked, the web application will typically build the query dynamically, as it is done in one of my own extensions for Dreamweaver. For example, if the column heading link looked like this:
<a href="/myresults.asp?sort=ProductName&direction=desc">Product Name</a>
you would retrieve the query string variables on the linked page and build a SQL statement dynamically:
ASP
SQLstring = "SELECT ProductName, UnitPrice FROM Products"
SQLstring = SQLstring & "
ORDER BY " & Request("sort")
SQLstring = SQLstring & " " & Request("direction")
ColdFusion
<cfset SQLstring = "SELECT ProductName, UnitPrice FROM
Products ORDER BY #url.sort# #url.direction#">
After building the string, you simply execute it from the web application.
Using a stored procedure is somewhat different, however, because your SQL is stored in the database. Unfortunately, you cannot pass column names to a SQL statement. In other words, this will not work:
CREATE PROCEDURE spGetProducts
@sort varchar(100),
@direction varchar(5)
AS
SELECT ProductName, UnitPrice FROM Products
ORDER BY @sort @direction
You could pass the sort and direction parameters to the database and build the statement dynamically within the stored procedure, however:
CREATE PROCEDURE spGetProducts
@sort varchar(100),
@direction varchar(5)
AS
-- Create a string variable
DECLARE @SQLstring varchar(1000)
-- build the SQL dynamically
SET @SQLstring = "SELECT ProductName, UnitPrice
FROM Products
ORDER BY ' + @sort + ' ' + @direction
-- Execute the SQL string
exec(@SQLstring)
As I said earlier, you lose many of the benefits of stored procedures by doing this. Note that it is still faster than executing the SQL statement from the web application, however there is a better way.
Using the Case statement in SQL is somewhat of an art, as it is a bit cumbersome to use in many situations. In some languages the Case statement gives you conditional execution logic. In SQL, it behaves more like an IF statement. We can use it to create a dynamic SQL statement on the fly. We'll pass the sort and direction parameters to the stored procedure just like in the previous example, however this time we'll create a strored procecure that will be compiled and will execute much more quickly:
CREATE PROCEDURE spGetProducts
@sort varchar(100),
@direction varchar(5)
AS
-- Execute different statements for ASC and DESC
IF @direction = 'desc' BEGIN
SELECT ProductName, UnitPrice FROM Products
ORDER BY
CASE @sort WHEN 'ProductName' THEN
ProductName ELSE NULL END DESC,
CASE @sort WHEN 'UnitPrice' THEN
UnitPrice ELSE NULL END DESC
END
-- Else, must be ASC
ELSE BEGIN
SELECT ProductName, UnitPrice FROM Products
ORDER BY
CASE @sort WHEN 'ProductName' THEN
ProductName ELSE NULL END ASC,
CASE @sort WHEN 'UnitPrice' THEN
UnitPrice ELSE NULL END ASC
END
GO
The SQL looks more complex, but it is really simple and one of the only ways to dynamically order a SQL statement within the confines of the language. You are simply saying "If the direction is DESC, use this set of statements, otherwise use the other set." That divides the code into two sections. Within the ASC and DESC sections you are saying "SELECT my fields and if the @sort parameter is 'ProductName', order by the ProductName field. If @sort is 'UnitPrice', order by the UnitPrice field." The key to this statement is the NULL keyword -- it is necessary using the CASE syntax in this situation, because the field types are different. In other words, you could not do this:
SELECT ProductName, UnitPrice FROM Products
ORDER BY
CASE @sort WHEN 'ProductName' THEN
ProductName ELSE
UnitPrice END ASC
You would get a syntax error:
---------------------------
Microsoft SQL-DMO (ODBC SQLState: 42000)
---------------------------
Error 257: Implicit conversion from data type nvarchar to money is not allowed. Use the CONVERT function to run this query.
However, if you were sorting ONLY varchar fields, the statement would work. It's just one of the idiosyncrasies of SQL. For example, this would work using the Employees table of the Northwind database, because the sort fields are all varchar types:
CREATE PROCEDURE spGetEmployees
@sort varchar(100),
@direction varchar(5)
AS
IF @direction = 'desc' BEGIN
SELECT * FROM Employees
ORDER BY
CASE @sort
WHEN 'LastName' THEN LastName
WHEN 'Firstname' THEN Firstname END DESC
END
IF @direction = 'asc' BEGIN
SELECT * FROM Employees
ORDER BY
CASE @sort
WHEN 'LastName' THEN LastName
WHEN 'Firstname' THEN Firstname END ASC
END
GO
Using Dreamweaver, stored procedures are almost as easy to use as recordsets. Using the spGetProducts stored procedure above, we'll create a basic page (the ASP and ColdFusion pages are included in the article download) that uses a dynamic heading sort. The steps are as follows:
1. Create the stored procedure code. Click the plus sign (+) in the Bindings panel and choose Stored Procedure. The procedures for setting this up vary for the different server models, but what you will need to do is set it up so that it uses the Northwind database (a data source needs to be set up in advance.) The two incoming parameters (@sort and @direction) should be fed by two URL or QueryString variables. You can also set up default values for these so that the page will work even if no link is clicked. The default @sort should be "ProductName" and the default @direction should be "ASC". Also, make sure the stored procedure code returns a recordset (called rsProducts in Figures 1 and 2 below.)
Figure 1: Setting up the stored procedure for ColdFusion in Dreamweaver
Figure 2: Setting up the stored procedure for ASP in Dreamweaver
2. In the Application tab of the insert bar (or the insert menu) choose Dynamic Table. Choose to show all records. Test the page to make sure you are getting results at this point.
3. Set up the ascending/descending switch with a bit of code. You want the headings to toggle asc and desc, so the code will be as follows:
ASP
<%
If Request("direction") = "asc" Then
direction = "desc"
Else
direction = "asc"
End If
%>
ColdFusion
<cfif url.direction EQ 'asc'>
<cfset direction = "desc">
<cfelse>
<cfset direction = "asc">
</cfif>
4. Set up the column headings. The sort parameter will be hard-coded into the link depending on the column name:
ASP
<tr>
<td>
<a href="products.asp?sort=ProductName&direction=<%=direction%>">Product Name</a>
</td>
<td>
<a href="products.asp?sort=Unitprice&direction=<%=direction%>">Unit Price</a>
</td>
</tr>
ColdFusion
<tr><cfoutput>
<td>
<a href="products.cfm?sort=PRODUCTNAME&direction=#direction#">Product Name</a>
</td>
<td>
<a href="products.cfm?sort=Unitprice&direction=#direction#">Unit Price</a>
</td></cfoutput>
</tr>
If you test the page now, the sortable heading should work. If you need to add more columns to the sort, you have to simply add more case statements inside the stored procedure.
In this article you learned how to add dynamic sorting to a stored procedure without resorting to using dynamic SQL, which can slow down performance.