Dynamic ORDER BY Clause

kenm — Tue, 06 Dec 2005 22:25:00 GMT

I was curious whether you could sort the resultset of a sproc based on dynamic parameters. For instance, I may want to sort a list of Northwind.Employees by LastName in some cases or HireDate in others. There are a few different ways I could do this:

Create different sprocs with the same SELECT and WHERE clauses but different ORDER BY clauses. This would be the most efficient from a database performace standpoint but the duplication of logic would be bad for code maintenance, especially if the sproc has very complex logic.
Leave out the ORDER BY clause or give it the most commonly used sort column, then re-sort the data in the middle-tier or client. This is a viable alternative but can cause performance issues on a web server.
Pass a parameter into the sproc that tells it which column to sort on.

The last option seemed to me to be the best since SQL Server is much better optimized at sorting data. However, I wasn't sure if it was possible to put conditional logic in the ORDER BY clause or how to do it. After a quick search I found this article which described exactly what I needed but with one caveat:

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

This was not good because the casting and converting could cause my sort to be unreliable for some datatypes, such as ordering by datetimes that have been cast as varchars. I did a little testing and found that the article is wrong about this point. It is possible to have the dynamic ORDER BY return different datatypes if you use a separate CASE statement for each possible option. Here's an example:

DECLARE @orderBy varchar(50)
SET @orderBy = 'LastName'

SELECT
    *
FROM
    Employees
ORDER BY
    CASE
        WHEN @orderBy = 'LastName' THEN LastName
    END,
    CASE
        WHEN @orderBy = 'HireDate' THEN HireDate
    END

Ken McNamee

Dynamic ORDER BY Clause