Overview: Often times, ASP developers will need to display information onto a web page that comes from a database or other data store. As you become more familiar with SQL server (and ASP), you will learn that using stored procedures will decrease your process time, while keeping server response times and server acitivty to a minimum.
A stored procedure is basically a compiled version of your SQL statement. Think of it as a method or a function you can call against your SQL server, which will go fetch records, update, insert, delete, or whatever you ask it to do. A stored procedure will also accept arguments. This means you can have variables in your stored proc which will act based on what you (or the user) specifically wants.
Problem: A common problem is where a developer wants to display data on your HTML/ASP page in a table. For our example, we will assume this information is a list of employee names, with email addresses, position, department, and phone numbers. Ideally, you would display this information by default in a sorted manner by name. (Last name or first name irrelevant for this discussion). However, you do not want to limit the user to only this sort. Commonly, a web developer will allow the header of the row to be a hyperlink, which resorts the data. This allows the user to quickly click, and redisplay the information.
The Code: The code may look something like this: æ - - - STORED PROC IS CALLED PREVIOUSLY AND LOADS RECORD SET - - -
æ - - - LOOP THRU YOUR RECORD SET AND SHOW DATA - - - Do While Not(RecordSet.EOF) Response.Write ô<tr><td>ö & RecordSet(ôfnameö) & ô</td>ö Response.Write ô<tr><td>ö & RecordSet(ôlnameö) & ô</td>ö Response.Write ô<tr><td>ö & RecordSet(ôphoneö) & ô</td>ö Response.Write ô<tr><td>ö & RecordSet(ôposö) & ô</td>ö Response.Write ô<tr><td>ö & RecordSet(ôdeptö) & ô</td>ö Response.Write ô<tr><td>ö & RecordSet(ôemailö) & ô</td>ö RecordSet.MoveNext Loop Response.Write ô</table>ö
The Stored Procedure: Now lets take a look at the stored procedure. (this syntax is written for MS SQL Server 2000).
CREATE PROC myProc @SortBy int AS SELECT fname, lname, phone, pos, dept, email FROM directory ORDER BY CASE WHEN @SortBy = 1 THEN fname WHEN @SortBy = 2 THEN lname WHEN @SortBy = 3 THEN pos WHEN @SortBy = 4 THEN dept END GO
This procedure accepts one argument, which is treated as an int. The int is used in the case statement to determine how to run the sort. The key is using the CASE/WHEN statement. This statement is allowed to be used inside your WHERE clause, where the IF statement is not.
ASP Code (Call Procedure): Note that the url on your table should be pointing to itself. At the beginning of your page, your VBScript should process the QueryString, to determine how to properly call the proc.
Dim iSort, SQL iSort = Request.QueryString(ôSortIDö) If (Trim(iSort) = ôö) Then iSort = 1 æ - - - DEFAULT TO SORT BY NAME - - -
æ - - - CONNECT TO YOUR DATABASE USING ADO OR WHATEVER - - - SQL = ôEXEC myProc ô & iSort SQL.Execute æ - - - THIS IS GENERIC, BASICLY, RUN YOUR QUERY - - -
æ - - - WRITE YOUR TABLE, AS SHOWN IN CODE ABOVE - - -
Other Ways To Sort: Be sure when you do this type of sort, you are aware that you are forcing the database to reload the entire contents of your page, and your database must redeliver the information. If you are working with large amounts of data, this can become very inefficient. There are two work-arounds which are simple, and do not require a great deal of code.
Alt #1: Load the data into a client side two dimensional array, and sort based on desired column. It is always better to let the client browser do the ôgrunt workö for sorting, error checking, or cleaning up small bits of code. This keeps the communication with the server at a minimum. This method would allow any number of users to sort as many times as they need to, and both the web server and SQL server will only be hit the initial time.
Alt #2: Perhaps a simpler, and more popular work around is to simply display less data. It might be wise to include a ôtopö argument in your Stored Procedure which will only return a certain amount of records at a given time. Give the user a text box, or pull down with the option to select their own max count to be returned. You could then modify your Stored Procedure as follows:
CREATE PROC myProc @SortBy int, @TopCount int AS SET rowcount @TopCount SELECT fname, lname, phone, pos, dept, email FROM directory ORDER BY CASE WHEN @SortBy = 1 THEN fname WHEN @SortBy = 2 THEN lname WHEN @SortBy = 3 THEN pos WHEN @SortBy = 4 THEN dept END GO
Conclusion: There are countless ways to do anything when it comes to programming. There is not really one right way to do everything. It will depend on the circumstances of your particular project, and the elements there of. I hope what I have provided here will be somewhat useful in your work, or will at least provide some alternative ideas to how you are currently doing things.