By Scott Mitchell
ORDER BY Statements in Stored Procedures
When creating data-driven Web applications, it is ideal to place the actual SQL statements into stored procedures, and to have the Web pages, then, call the appropriate stored procedures rather than executing the SQL statements themselves. (If you are unfamiliar with what stored procedures are, or their benefits over in-line SQL, be sure to check out Rob Howard's blog entry on why to use stored procedures. Also check out articles here that discuss how to create a stored procedure in SQL Server, how to call a stored procedure from a classic ASP page, and how to call a stored procedure using VB.NET.)
Imagine that you have a stored procedure that returns, say, the employees from the
This stored procedure's code might look like:
Calling this stored procedure, then, would return a list of the employees ordered alphabetically by the names of the
employees. To display this data in, say, an ASP.NET Web page, you'd simply need to bind the results of the stored
procedure call to a DataGrid. But what if you want to have the results of the stored procedure ordered some other way?
That is, what if sometimes you want to call
getEmployees and have the results ordered by the employees' names,
and other times you want to call
getEmployees and have the results ordered by their salaries?
In this article we will look at how to create a stored procedure whose results can be dynamically reordered.
Dynamically Ordering Stored Procedure Results
Before we examine how to dynamically order the results of a stored procedure, let's first discuss why we might want to do such a thing. A common need for dynamically ordered stored procedures is when building sortable DataGrids. The ASP.NET DataGrid Web control provides built-in sorting support that allows the end-user to click on a column heading to sort the results of the DataGrid by the values in that particular column. When the end-user clicks a column heading, the data needs to be rebound to the DataGrid in the requested sort order. There are a number of ways to accomplish this, but a common one is to simply reissue the SQL query, but modifying the
ORDER BY clause so that the results are properly ordered. This is easy to do when using in-line SQL
statements embedded into the ASP.NET Web page, but when using stored procedures, the solution isn't nearly as clear cut.
(For more information on sortable DataGrids be sure to read An
Extensive Examination of the DataGrid Web Control: Part 4.)
There are two basic approaches to building dynamically orderable stored procedures:
- Pass in the
ORDER BYclause as a parameter to the stored procedure. In the stored procedure, build up the SQL statement in a string and then execute this statement using
- Pass in the column to sort by and then use a
CASEstatement in the
ORDER BYclause to order the results according to the input parameter value.
Let's examine each of these two approaches in detail. The next section looks at building a dynamic SQL statement in
a stored procedure; the section following that looks at the second approach, using
Dynamic Ordering By Issuing Dynamic SQL Statements
Stored procedures are great vehicles for issuing static SQL statements. However, there are times when the columns that need to be returned, or the columns that need to appear in the
WHERE clause are dynamic. In our
case, the columns that appear in the
ORDER BY clause are dynamic. Essentially, we want to create a
stored procedure that looks something like:
We could then, ideally, pass in to the
@OrderByClause parameter the string "
and the results returned from the stored procedure would be ordered by the employees' names. Unfortunately the above
syntax won't work.
What we need to do instead is dynamically construct a string that contains the SQL statement we want to execute. Once
this string has been built up, we can execute the composed SQL query by calling the
as shown below:
This approach of dynamically constructing a SQL statement and then using
EXEC to execute the query is
discussed further in the article: Running Dynamic Stored Procedures.
To use this approach from an ASP or ASP.NET Web page, you'd simply call the stored procedure setting the input parameter
@OrderByClause to the value of the columns to order the results by. Specifically, the parameter's value should
be set to the SQL that would appear immediately after the
ORDER BY keyword in an in-line SQL statement. That is, to have the results ordered
Salary field in descending order, you'd execute the stored procedure using:
getEmployees 'Salary DESC'.
Dynamic Ordering Using
While the dynamic SQL approach works, personally I find it sloppy. One major disadvantage of it is that it greatly reduced the stored procedure's readability. This is especially true with larger dynamic SQL statements, as you have to create this SQL string and there's no helpful syntax coloring or other indications if you make a mistake. For example, if when typing in your SQL statement you forget a space between two field names, you won't get an error saving the stored procedure, but you will get a rather cryptic error when executing the stored procedure. Anyone who has created their fair share of dynamic stored procedures has no doubt run into such problems that took way too long to find the source of the error...
A somewhat cleaner approach is to allow the SQL statements to be dynamically ordered by placing a
statement in the
ORDER BY clause. The
CASE statement is a SQL statement that is typically used in
SELECT clause of a SQL statement, but can appear in the
ORDER BY clause as well. The following
example illustrates a SQL query with a
CASE statement in the
To dynamically order the results of a SQL query in a stored procedure we can use a
CASE statement in
ORDER BY clause like so:
Note that the input parameter is a
varchar and can specify one of the column names to sort by. The
CASE statement in the
ORDER BY clause determines what column to sort by based upon the
value passed in.
There are a couple caveats when using this approach for dynamic ordering. First, dynamic ordering doesn't work for
mixed data types. That is, if you have fields of differing data types that you want to order by - such as
decimals, as is the case with
Salary above - then in the
CASE statement you need to use
CONVERT to convert the mismatched data type to the same
data type as the others. Note how in the second
WHEN line in the example above,
converted to a
varchar(50). You'll have to do this with
int and other
non-character data types as well.
Another downside of this approach is that only one column can be sorted by. That is, you cannot include a comma-delimited
list of columns, such as
LastName, FirstName. One workaround is to use string concatenation in the
THEN class. For example, to sort by
LastName, FirstName we could do:
For a more in-depth discussion of using
CASE to dynamically order the results of a stored procedure be
sure to check out this messageboard thread.
In this article we examined two techniques for creating dynamically sortable stored procedures. The first approach was an approach we had examined before in a past article - namely, that of using dynamic stored procedures. This involves concocting a SQL statement in a string variable and then using
EXEC to dynamically execute the crafted
query. The second approach involved using a
CASE statement in the
ORDER BY clause of the
Both approaches allow for a stored procedure to accept an input parameter that determines the column (or columns) by which the results will be ordered. As discussed, this has particular use with sortable DataGrids.