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:
and so on.... IF @ColumnNumberasInteger = 1 SELECT * FROM mYTable ORDER BY 1 ELSE IF @ColumnNumberAsInteger = 2 SELECT * FROM MyTable ORDER BY 2 ...
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?
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:
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:
In my opinion, Dave's solution is by far the most elegant and easy to write/maintain.
P.S. I just thought of a fourth possibility while I was writing this... If you're using SQL 7.0, check
stored procedure. It's basically the same as using the dynamic
EXECUTE, but you can parameterize the query.