Dynamic ORDER BY

By Garth Wells on 22 January 2001 | Tags: ORDER BY

We've got another article chock full of SQL goodness from Garth. This one covers creating a dynamic ORDER BY clause for your SELECT statements. It's a follow up to his earlier article on dynamic WHERE clauses.

In my previous article I demonstrated how to use the COALESCE function to create a dynamic WHERE clause. In this article, which is based on solutions posted in the public newsgroup forums, I'll show you how to use the CASE function to implement a dynamic ORDER BY. In the end, I'll use both techniques together to create a stored procedure that supports both a dynamic WHERE and ORDER BY.

The ORDER BY Clause

SQL Server retrieves data in random order. Some developers errantly think that when a simple SELECT (as shown below) is executed the data is retrieved in the order in which it was entered.

USE Northwind
go
SELECT CompanyName,
       ContactName,
       ContactTitle
FROM Customers

Although it may be returned in the order in which it was entered, the only way to ensure order is with the ORDER BY clause. The following shows how to sort the resultset on company name in descending order.

SELECT CompanyName,
       ContactName,
       ContactTitle
FROM Customers
ORDER BY CompanyName DESC

The CASE Statement

The CASE statement is used to add conditional logic to Transact-SQL statements. The statements listed below show to use CASE to SELECT a message that is reflective of the time of day.

DECLARE @DateTime datetime
SET @DateTime = '13:00:01'
SELECT @DateTime

SELECT CASE WHEN DATEPART(hour,@DateTime) < 12 THEN 'Good Morning'
            WHEN DATEPART(hour,@DateTime) >= 13 
              AND DATEPART(hour,@DateTime) < 18 THEN 'Good Afternoon'
            ELSE 'Good Evening'
       END

--Results--

Good Afternoon

Notice that I did not include the date portion when setting the value for the @DateTime variable. When only the time portion is provided, the date is set to 01/01/1900. The CASE statement uses the DATEPART function to determine the hour and then returns the appropriate greeting.

Dynamic ORDER BY

Now that we've covered the basics, let's see how CASE can be used to implement a dynamic ORDER BY. Assume you want to sort the results of the Customers query on any of the columns referenced in the SELECT. The following statements show how this is done.

DECLARE @SortOrder tinyint
SET @SortOrder = 2

SELECT CompanyName,
       ContactName,
       ContactTitle
FROM Customers
ORDER BY CASE WHEN @SortOrder = 1 THEN CompanyName
              WHEN @SortOrder = 2 THEN ContactName
              ELSE ContactTitle
         END

A variable is used to specify the desired sort and CASE resolves the value to the appropriate column name. This approach is obviously best suited for a stored procedure, so let's create one with the following.

CREATE PROCEDURE ps_Customers_SELECT_DynamicOrderBy
@SortOrder tinyint = NULL
AS
SELECT CompanyName,
       ContactName,
       ContactTitle
FROM Customers
ORDER BY CASE WHEN @SortOrder = 1 THEN CompanyName
              WHEN @SortOrder = 2 THEN ContactName
              ELSE ContactTitle
         END

You can then call the procedure with any of the statements listed below.

EXEC ps_Customers_SELECT_DynamicOrderBy
EXEC ps_Customers_SELECT_DynamicOrderBy 1
EXEC ps_Customers_SELECT_DynamicOrderBy 2

Dynamic WHERE + ORDER BY

In one of the examples I included in my book, Code Centric: T-SQL Programming with Stored Procedures and Triggers, I show how to use both COALESCE and CASE to create a stored procedure that supports both a dynamic WHERE and ORDER BY. You can see the final results of the example by going here: www.SQLBook.com/SearchScreen.asp

The search screen allows you to specify the sort to order the data and one or more parameters to filter it. You will also notice that the number of rows returned are shown on the page. The procedure that retrieves the data is shown here.

CREATE PROCEDURE ps_Player_SELECT_ByStats (
@OrderBy tinyint = 1,
@Pla_BattingAverage smallint = NULL,
@Pla_HomeRuns smallint = NULL,
@Pla_RBIs smallint = NULL,
@Pla_StolenBases smallint = NULL,
@RowsReturned smallint = NULL OUTPUT )
AS
SET NOCOUNT ON

SELECT Pla_FName+' '+Pla_LName AS Name,
       Pla_BattingAverage,
       Pla_HomeRuns,
       Pla_RBIs,
       Pla_StolenBases
FROM Players
WHERE Pla_BattingAverage >= COALESCE(@Pla_BattingAverage,0) AND
      Pla_HomeRuns >= COALESCE(@Pla_HomeRuns,0) AND
      Pla_RBIs >= COALESCE(@Pla_RBIs,0) AND
      Pla_StolenBases >= COALESCE(@Pla_StolenBases,0)
ORDER BY CASE WHEN @OrderBY = 1 THEN Pla_BattingAverage
              WHEN @OrderBY = 2 THEN Pla_HomeRuns
              WHEN @OrderBY = 3 THEN Pla_RBIs
              WHEN @OrderBY = 4 THEN Pla_StolenBases
              END DESC

SET @RowsReturned = @@ROWCOUNT

The use of COALESCE is slightly different than in the previous article. I used zero instead of the column names because it is a little easier to understand the logic. None of the columns will contain NULLs or negative values, so they will always be greater than or equal to zero. This ensures the comparison will evaluate to true when a parameter value is not supplied.

The search screen (SearchScreen.asp) portion of the example is what I call "completing the example." Quite a few of the examples in my book are "completed" by showing you how to access the SQL Statements via a GUI created with ASP. My research indicated that developers not only want to see how to create SQL statements within Query Analyzer, but also want to see how they are accessed with a programming language used to create a front-end application. If you like to work through these type of examples, I am sure you will enjoy my book.