When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Wednesday, January 07, 2004

Dynamic ORDER BY Statements in Stored Procedures

By Scott Mitchell


Introduction
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.)

- continued -

'

Imagine that you have a stored procedure that returns, say, the employees from the Employees table. This stored procedure's code might look like:

CREATE PROCEDURE getEmployees AS

SELECT EmployeeID, FirstName, LastName, SSN, Salary
FROM Employees
ORDER BY LastName, FirstName

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:

  1. Pass in the ORDER BY clause 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 EXEC or sp_ExecuteSql.
  2. Pass in the column to sort by and then use a CASE statement in the ORDER BY clause 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 CASE statements.

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:

CREATE PROCEDURE getEmployees ( @OrderByClause varchar(100) ) AS

SELECT EmployeeID, FirstName, LastName, SSN, Salary
FROM Employees
ORDER BY @OrderByClause

We could then, ideally, pass in to the @OrderByClause parameter the string "LastName, FirstName," 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 EXEC function, as shown below:

CREATE PROCEDURE getEmployees ( @OrderByClause varchar(100) ) AS

-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
    
-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = 'SELECT EmployeeID, FirstName, LastName, SSN, Salary ' +
                       'FROM Employees ORDER BY ' + @OrderByClause
    
-- Execute the SQL statement
EXEC(@SQLStatement)

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 by the Salary field in descending order, you'd execute the stored procedure using: getEmployees 'Salary DESC'.

Dynamic Ordering Using CASE Statements
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 CASE statement in the ORDER BY clause. The CASE statement is a SQL statement that is typically used in the 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 SELECT clause:

SELECT FirstName, LastName,
       CASE WHEN Salary < 20000 THEN 'Not so much'
            WHEN Salary >= 20000 AND Salary < 100000 THEN 'Better'
            WHEN Salary >= 100000 AND Salary < 250000 THEN 'Big bucks!'
            ELSE 'Holly cow!'
       END As WealthInfo
FROM Employees
ORDER BY LastName, FirstName

The value in the third field, WealthInfo, will depend upon the employee's salary. For a more thorough look at the CASE statement check out: The Case for CASE and CASE Statement Tricks.

To dynamically order the results of a SQL query in a stored procedure we can use a CASE statement in the ORDER BY clause like so:

CREATE PROCEDURE getEmployees ( @ColumnName varchar(100) ) AS

SELECT EmployeeID, FirstName, LastName, SSN, Salary
FROM Employees
ORDER BY
  CASE WHEN @ColumnName='LastName' THEN LastName
       WHEN @ColumnName='Salary' THEN CONVERT(varchar(50), Salary)
       WHEN @ColumnName='SSN' THEN SSN
  END

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 varchars and decimals, as is the case with LastName and 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, Salary is converted to a varchar(50). You'll have to do this with datetime, 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:

CREATE PROCEDURE getEmployees ( @ColumnName varchar(100) ) AS

SELECT EmployeeID, FirstName, LastName, SSN, Salary
FROM Employees
ORDER BY
  CASE WHEN @ColumnName='LastName' THEN CONVERT(char(50), LastName) + CONVERT(char(50), FirstName)
       WHEN @ColumnName='Salary' THEN CONVERT(varchar(50), Salary)
       WHEN @ColumnName='SSN' THEN SSN
  END

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.

Conclusion
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 SQL query.

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.

Happy Programming!

  • By Scott Mitchell



  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article