Question: Can a variable choose the ORDER BY column and direction without building dynamic SQL?
Answer: Yes. Use separate CASE expressions for the allowed columns and directions, validate the choices, and add a stable tiebreaker.
After fourteen years in the industry, a client evaluating me for a tuning project asked this familiar question. For a moment I thought they should already know I could answer it. Then I realized it was a chance to show the reasoning. We used FirstName and LastName from AdventureWorks, with an ascending or descending choice for either one.
| Column | Direction |
|---|---|
| FirstName | ASC |
| FirstName | DESC |
| LastName | ASC |
| LastName | DESC |
USE AdventureWorks2025;
DECLARE @OrderBy varchar(10) = 'LastName';
DECLARE @OrderByDirection varchar(4) = 'ASC';
IF @OrderBy NOT IN ('FirstName', 'LastName') OR @OrderBy IS NULL
THROW 50001, 'Choose FirstName or LastName.', 1;
IF @OrderByDirection NOT IN ('ASC', 'DESC') OR @OrderByDirection IS NULL
THROW 50001, 'Choose ASC or DESC.', 1;
SELECT TOP (20) BusinessEntityID, FirstName, LastName
FROM Person.Person
ORDER BY
CASE WHEN @OrderBy = 'FirstName' AND @OrderByDirection = 'ASC'
THEN FirstName END ASC,
CASE WHEN @OrderBy = 'FirstName' AND @OrderByDirection = 'DESC'
THEN FirstName END DESC,
CASE WHEN @OrderBy = 'LastName' AND @OrderByDirection = 'ASC'
THEN LastName END ASC,
CASE WHEN @OrderBy = 'LastName' AND @OrderByDirection = 'DESC'
THEN LastName END DESC,
BusinessEntityID ASC;Only the selected CASE returns a name; the others return NULL for all rows and don’t affect their relative ordering. Change the variables to exercise all four choices. BusinessEntityID makes ties deterministic. TOP (20) keeps this display small; remove it when you need the full result.
CASE Is an Option, Not a Universal Speed Rule
I liked avoiding four nearly identical queries for that application. Dynamic SQL is not automatically slow, though. A carefully parameterized query with an allowed column list can be a good design, and sp_executesql can reuse plans. Never concatenate an unchecked user-supplied identifier or direction.
CASE-based ordering can require a Sort even when a simple column ordering could use an index. If you add numeric or date columns, keep expressions separated by compatible data types; one CASE picks its return type by precedence and can cause unwanted conversions. Compare plans for the actual application rather than choosing a winner from the syntax alone.
And yes, I got the project, and billed six days of consulting on it. A familiar interview question can still lead to a useful conversation.
A dynamic sort is not a reason for dynamic SQL, it is a few CASE expressions and a tiebreaker.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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:
Another 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.