4GuysFromRolla.com - 4GuysFromRolla.com

Windows Systems Administrator
Jupitermedia
US-CT-Darien

The SQL Guru Answers your Questions...

Today's question is a popular message board question:

A commonly asked question on the message board is: Is it possible to have parameterized ORDER BYs? For example, is it possible to do something like:
 
CREATE PROCEDURE blah (@ColumnNumberAsAnInteger int)
AS

SELECT * FROM MyTable
ORDER BY @ColumnNumberAsAnInteger
In an attempt to have this question answered (it is a stumper for me!), I sent it off to Sean, our SQL Guru. Here is the email I sent him:
I wanted to do this earlier at Microsoft, and ended up doing:
...
IF @ColumnNumberasInteger = 1
	SELECT * FROM mYTable
	ORDER BY 1
ELSE IF @ColumnNumberAsInteger = 2
	SELECT * FROM MyTable
	ORDER BY 2
...
and so on.
It would be really nice to be able to pass in just a column name or number. Is this at all possible? If not, are there any other workarounds other than the way I showed?
Thanks!

Scott,

This is sort of possible, there are about three tricks I know of that give the functionality you want. (This applies to SQL Server 6.5. I don't remember if I've tried the "ORDER BY @ColumnNumberAsAnInteger" syntax in 7.0. And since I don't have a 7.0 server in front of me as of this writing, I'll let you check.)

You've already mentioned the first trick - using a gigantic IF block to handle the different ORDER BYs. This solution is a real pain because it usually means that you're duplicating the query... which can make the SP hard to maintain.

The second trick is to put the query into string variables and use a dynamic EXECUTE statement. Such as:

CREATE PROCEDURE foo (@ColumnNumberAsAnInteger int) AS BEGIN DECLARE @mysql varchar(100) SELECT @mysql = 'SELECT * FROM bar ORDER BY ' + convert(varchar, @ColumnNumberAsAnInteger) EXECUTE(@mysql) END

So, this isn't too bad. Until, of course, you start writing complex queries and having to stick them into strings. Especially under SQL Server 6.5, these can become difficult - difficult to write, debug, and maintain. Under 7.0, it gets a little easier with the honkin' big varchars, but there are other issues surrounding dynamic executes.

The next solution I can't take credit for. David Besch, a friend, fellow employee, and fellow guru, came up with this one (a couple of weeks before you asked the question, acutally... my company often needs to do dynamic ORDER BYs for customer websites). If you don't mind returning a little extra data with your query, you can do this:

SELECT FirstName, LastName, CASE WHEN @ColumnNumberAsAnInteger = 1 THEN FirstName WHEN @ColumnNumberAsAnInteger = 2 THEN LastName ELSE 1 END AS SortCol ORDER BY SortCol

In my opinion, Dave's solution is by far the most elegant and easy to write/maintain.

-Sean

P.S. I just thought of a fourth possibility while I was writing this... If you're using SQL 7.0, check out the sp_executesql stored procedure. It's basically the same as using the dynamic EXECUTE, but you can parameterize the query.

Read Other SQL Guru Questions