How do I do a server side sort a record set, by passing a variable to a stored procedure?

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 - - -

æ - - - CREATE HEADER WITH URL LINKS - - -
Response.Write ô<table>ö
Response.Write ô<tr><th><a href=Æmypage.asp?SortID=1Æ>First Name</a></th>ö
Response.Write ô<th><a href=Æmypage.asp?SortID=2Æ>Last Name</a></th>ö
Response.Write ô<th>Phone</th>ö
Response.Write ô<th><a href=Æmypage.asp?SortID=3Æ>Position</a></th>ö
Response.Write ô<th><a href=Æmypage.asp?SortID=4Æ>Department</a></th>ö
Response.Write ô<th>Email</th>ö

æ - - - 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.

This requires converting a 2 dimensional VBScript array into a into a JavaScript array. The function below will convert this for you. You would then create a function that sorts the array based on the element you pass. This function actually allows a Dimension flag to choose if you are passing a 1 or 2 dimension array.

' - - - COPY A VBSCRIPT ARRAY INTO A JAVASCRIPT ARRAY - - -
Sub Convert_VB_to_JS_Array(iArray, iDimension, Temp)
Dim ArrUbound_1, ArrUbound_2
Select Case (iDimension)
Case 1 ' - - - 1 Dimension Array - - -
ArrUbound_1 = UBound(iArray,1)
%>
<script language='JavaScript' type='text/Javascript'>
var JSArray<%=Temp%> = new Array();
<% for iCounter = 0 to ArrUbound_1 %>
<%Tempi = Replace(iArray(iCounter), "'", " ")%>
JSArray<%=Temp%>[<%=iCounter%>] = '<%=Tempi%>';
<% next %>
</script>
<%
Case 2 ' - - - 2 Dimension Array - - -
ArrUbound_1 = UBound(iArray,1)
ArrUbound_2 = UBound(iArray,2)
%>
<script language='JavaScript' type='text/Javascript'>
var JSArray<%=Temp%> = new Array();
<% for iCounter = 0 to ArrUbound_2 %>
JSArray<%=Temp%>[<%=iCounter%>] = new Array();
<% for jCounter = 0 to ArrUbound_1 %>
<%Tempi = Replace(iArray(jcounter, iCounter), "'", " ")%>
JSArray<%=Temp%>[<%=iCounter%>][<%=jCounter%>] = '<%=Tempi%>';
<% next %>
<% next %>
</script>
<%
End Select
End Sub

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.

Regards;
Mark Hansen (ZZFive03)