Sort Query Using Dynamic Variables Without EXEC – Interview Question of the Week #074

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.

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)