How do I use a variable in an ORDER BY clause?
Often, we want to allow users to determine how their data will be ordered. So, let's say we have a table like this:
The first thing people tend to try in T-SQL is the following:
However, this returns:
Clearly, SQL Server doesn't like variables in the ORDER BY clause. Well, we can just use dynamic SQL, right?
But dynamic SQL is not a very desirable solution. (See Erland's text for some of the problems.)
And then we could always construct an ad hoc query to send to the database from ASP. But we all know what a bad idea that is, right?
So, are there other ways to skin this cat? Of course. Some people try this:
But this returns:
This is because you can't put an IF / THEN statement within a query expression—IF is used for program flow. And yes, you can construct an example like this:
However, this isn't going to be very fun if the number of columns is quite large, and if the SELECT query is complex, you probably don't want to repeat it for every possible column. And we haven't even introduced making the order direction (ASC/DESC) a parameter yet. :-)
Another workaround is to use a CASE expression in the ORDER BY clause. There are a couple of "rules" that you must follow. The most important is that all possible results of a CASE expression must be of the same datatype (or must have the correct data precedence to implicitly convert). So let's try a small example, where only email or firstname are allowed.
A rule you can take advantage of, in order to allow columns of different datatypes to be ordered conditionally, is that an ORDER BY step containing NULL is ignored. So, you can do this to separate conditionals based on each data type, without worrying about order taking precedence:
Now, what if you want to pass the ascending/descending order a variable as well? It gets a little more complicated, since the parser for T-SQL is very fussy about where that DESC keyword goes. Here is the example I came up with:
Now there is a little trick you can use for numeric values, to switch between ASC and DESC, with a little less logic than above. You can invert a column to negative so that it sorts the opposite way in the default order (ASC). Then you don't have to worry about the finnicky use of the ASC/DESC keywords. I'll use a simpler example to demonstrate its use:
I don't suggest you try this for a varchar column; you should get the following error:
Now of course, clean up:
Please let us know if you have any further problems using these examples, or if there are examples we might be missing.
Related ArticlesHow do I build a query with optional parameters?
How do I calculate the median in a table?
How do I create a store locator feature?
How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns?
How do I deal with multiple resultsets from a stored procedure?
How do I debug my SQL statements?
How do I determine if a column exists in a given table?
How do I enable or disable connection pooling?
How do I enumerate through the DSNs on a machine?
How do I find a stored procedure containing <text>?
How do I get a list of Access tables and their row counts?
How do I get the latest version of the JET OLEDB drivers?
How do I handle alphabetic paging?
How do I handle BIT / BOOLEAN columns?
How do I handle error checking in a stored procedure?
How do I ignore common words in a search?
How do I page through a recordset?
How do I present one-to-many relationships in my ASP page?
How do I prevent duplicates in a table?
How do I prevent my ASP pages from waiting for backend activity?
How do I prevent NULLs in my database from mucking up my HTML?
How do I protect my Access database (MDB file)?
How do I protect my stored procedure code?
How do I protect myself against the W32.Slammer worm?
How do I remove duplicates from a table?
How do I rename a column?
How do I retrieve a random record?
How do I return row numbers with my query?
How do I send a database query to a text file?
How do I simulate an array inside a stored procedure?
How do I solve 'Could not find installable ISAM' errors?
How do I solve 'Operation must use an updateable query' errors?
How do I temporarily disable a trigger?
How do I use a SELECT list alias in the WHERE or GROUP BY clause?
Should I index my database table(s), and if so, how?
Should I store images in the database or the filesystem?
Should I use a #temp table or a @table variable?
Should I use a view, a stored procedure, or a user-defined function?
Should I use recordset iteration, or GetRows(), or GetString()?
What are all these dt_ stored procedures, and can I remove them?
What are the limitations of MS Access?
What are the limitations of MSDE?
What are the valid styles for converting datetime to string?
What datatype should I use for my character-based database columns?
What datatype should I use for numeric columns?
What does "ambiguous column name" mean?
What is this 'Multiple-step OLE DB' error?
What is wrong with 'SELECT *'?
What naming convention should I use in my database?
What should I choose for my primary key?
What should my connection string look like?
When should I use CreateObject to create my recordset objects?
Where can I get this 'Books Online' documentation?
Where do I get MSDE?
Which database platform should I use for my ASP application?
Which tool should I use: Enterprise Manager or Query Analyzer?
Why are there gaps in my IDENTITY / AUTOINCREMENT column?
Why can I not 'open a database created with a previous version...'?
Why can't I access a database or text file on another server?
Why can't I use the TOP keyword?
Why do I get 'Argument data type text is invalid for argument [...]'?
Why do I get 'Not enough space on temporary disk' errors?
Why does ASP give me ActiveX errors when connecting to a database?
Should I use COALESCE() or ISNULL()?
Where can I get basic info about using stored procedures?