It does not matter which version of SQL Server we are using, I think this question is going to be there universally all the time when people want to sort query using dynamic variables. Trust me, this is indeed a popular question. After 14 years of experience in the industry, just last week, I was asked this question when one of my clients was evaluating me to tune their queries. Well, for a moment I thought the client should know that I know these kind of questions. Next moment, I realize that this is my opportunity to impress them as they do know the answer.
Question: How to sort (order by) query with dynamically passed variable in SQL Server?
Answer: A very common answer is to use dynamically built query, but personally I do not like the solution. I prefer any solution over using EXEC or SP_EXECUTESQL.
In today’s blog post we will see a solution where we will sort query using dynamic variable without using EXEC. So even though the original question did not specify if we can use EXEC or not, we will build our solutions without building the dynamic query. The reason for not using dynamic query is poor performance and difficult to manage as the application grows.
We will use CASE statement with dynamic variables in ORDER BY. There are lots of confusion in the industry about how to use the CASE statement in the ORDER BY clause so we will a very simple example.
Let us assume that a table has multiple columns, for example FirstName and LastName and application user can decide dynamically if they want to sort them by either of the column as well as their sort order (ascending or descending). For this example, we will depend on the AdeventureWorks sample database but trust me, you will find this situation in any database you are managing as a table with FirstName and LastName is very common.
USE AdventureWorks2014 GO SELECT FirstName, LastName FROM Person.Person GO
Now let us assume that there are user can either order query based on column FirstName or LastName or force sort order like Ascending or Descending. This means there are four options to order this query. Let us the options in the following table for easy illustration.
| Column Name | Sort Order | |
| Option 1 | FirstName | Ascending |
| Option 2 | FirstName | Descending |
| Option 3 | LastName | Ascending |
| Option 4 | LastName | Descending |
Now the option which we have is to have four different query based on what the user selects. However, writing four queries and managing them can be a nightmare. Think how difficult it can become if user have access to specify 10 different columns.
The best solution is to write ORDER BY clause with CASE statement. User can write one query and pass external variable to order the query with appropriate sort order. Let us see how we can write it.
USE AdventureWorks2014 GO -- Decleare the Variable DECLARE @OrderBy VARCHAR(10) DECLARE @OrderByDirection VARCHAR(4) -- Set the Variables SET @OrderBy = 'FirstName' SET @OrderByDirection = 'DESC' -- Now let us see how we can write case statement with order by clause SELECT FirstName, LastName FROM Person.Person ORDER BY -- FirstName CASE WHEN @OrderBy = 'FirstName' AND @OrderByDirection = 'ASC' THEN FirstName END, CASE WHEN @OrderBy = 'FirstName' AND @OrderByDirection = 'DESC' THEN FirstName END DESC, -- LastName CASE WHEN @OrderBy = 'LastName' AND @OrderByDirection = 'ASC' THEN FirstName END, CASE WHEN @OrderBy = 'LastName' AND @OrderByDirection = 'DESC' THEN FirstName END DESC GO
You can see in the above example, we have four different conditions in the ORDER BY clause.
This query is easy to manage as instead of four different queries we have to manage only one query. Additionally, as this is single query, we can put it in the stored procedure and can take advantage of the stored procedure.
Well, with this we end this question about sort query using dynamic variable.
If you are wondering if I got the performance tuning consultancy or not, of course after this example, I did get the project and I have already billed them for over 6 days of consulting time.
Reference: Pinal Dave (https://blog.sqlauthority.com)
12 Comments. Leave new
Is there anything wrong with using EXEC statements ?
this is also another good method of multiple order by in query, we are also using multiple order by but we send order by condition in store procedure via parameter.
Just small tip. Specify ELSE part in CASE statement. Always. I know, your code is identical to CASE WHEN … THEN ELSE NULL END but specify ELSE part is better for anyone who will read few years old code and trying to figure how to fix some stuff.
Or use syntax-sugar IIF
Found what seems to be an oversight.
The query is sorting by FirstName even for the two options where LastName is desired. It’s incorrect in both the written query and screenshot.
Does this method is allowed?
I’m not sure what MS mean by constant expresion.
Warrning from UpgradeAdvisoor:
Constant expressions are not allowed in the ORDER BY clause in 90 or later compatibility modes
“Constant expressions are allowed (and ignored) in the ORDER BY clause when the database compatibility mode is set to 80 and earlier. However, these expressions in the ORDER BY clause will cause the statement to fail when the database compatibility mode is set to 90 or later. Here is an example of such problematic statements: SELECT * FROM Production.Product ORDER BY CASE WHEN 1=2 THEN 3 ELSE 2 END”
Hey Pinal,
For the example there is typo mistake. For the last two cases order by should have been by Last name instead First name. The solution works perfectly and thank you for sharing this post. It is always helpful to read and get the things implemented from your Blogs.
Once SP has a plan, the plan is used for the SP again and again. What happens when the first SP is with one set of parameters and the one after it has a complete different set?
This technique works fine for a table with a small number of columns but try it against a table with 50+ columns, any of which can be the sort criteria and I think you would find the dynamic approach to be far more performant
How to i give more than one(example 5 columns) sort option using this option
And what about table selection using Dynamic Variables Without Exec?
SELECT * FROM CASE WHEN ‘Key’ < 1000 THEN Partition1 ELSE Partition2 END;
This approach is not so useful if one of the options is to sort by two or more columns, or if there is a mix of date / numeric / varchar fields.
I use this approach:
ORDER BY CASE @OrderBy WHEN 'Name' THEN ROW_NUMBER() OVER (ORDER BY Surname, FirstName) WHEN 'FirstName' THEN ROW_NUMBER() OVER (ORDER BY FirstName) WHEN 'DateOfBirth' THEN ROW_NUMBER() OVER (ORDER BY DateOfBirth) WHEN 'NumberOfWidgets' THEN ROW_NUMBER() OVER (ORDER BY NumberOfWidgets) ENDAnother example of an incomplete solution to the problem. Non-dynamic SQL solutions don’t allow for multiple sort columns nor do they allow for order of sort columns. Try sorting by LastName, then FirstName, LastName, then DateCreated, etc… now reverse the search orders… it doesn’t work. Writing all possible combinations is also not a good approach, not scalable, and not maintainable. Dynamic SQL is the only workable approach currently and If you generate dynamic SQL that uses parameters, then execution plans can also be cached.