Dynamic ORDER BYBy 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 ClauseSQL 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.
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.
The CASE StatementThe 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.
--Results--
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 BYNow 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.
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.
You can then call the procedure with any of the statements listed below.
Dynamic WHERE + ORDER BYIn 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.
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. |
- Advertisement - |