T-SQL Conditional Order By

Question

I am trying to write a stored procedure that returns a list of object with the sort order and sort direction selected by the user and passed in as sql parameters.

Lets say I have a table of products with the following columns: product_id(int), name(varchar), value(int), created_date(datetime) and parameters @sortDir and @sortOrder

I want to do something like

select *
from Product
  if (@sortOrder = 'name' and @sortDir = 'asc') 
  then order by name asc
  if (@sortOrder = 'created_date' and @sortDir = 'asc') 
  then order by created_date asc
  if (@sortOrder = 'name' and @sortDir = 'desc') 
  then order by name desc
  if (@sortOrder = 'created_date' and @sortDir = 'desc') 
  then order by created_date desc

I tried do it with case statements but was having problems since the data types were different. Anyone got any suggestions?

Aaron Bertrand · Accepted Answer · 2013-03-26T11:01:58.517

CASE is an expression that returns a value. It is not for control-of-flow, like IF. And you can't use IF within a query.

Unfortunately, there are some limitations with CASE expressions that make it cumbersome to do what you want. For example, all of the branches in a CASE expression must return the same type, or be implicitly convertible to the same type. I wouldn't try that with strings and dates. You also can't use CASE to specify sort direction.

SELECT column_list_please
FROM dbo.Product -- dbo prefix please
ORDER BY 
  CASE WHEN @sortDir = 'asc' AND @sortOrder = 'name' THEN name END,
  CASE WHEN @sortDir = 'asc' AND @sortOrder = 'created_date' THEN created_date END,
  CASE WHEN @sortDir = 'desc' AND @sortOrder = 'name' THEN name END DESC,
  CASE WHEN @sortDir = 'desc' AND @sortOrder = 'created_date' THEN created_date END DESC;

An arguably easier solution (especially if this gets more complex) is to use dynamic SQL. To thwart SQL injection you can test the values:

IF @sortDir NOT IN ('asc', 'desc')
  OR @sortOrder NOT IN ('name', 'created_date')
BEGIN
  RAISERROR('Invalid params', 11, 1);
  RETURN;
END

DECLARE @sql NVARCHAR(MAX) = N'SELECT column_list_please
  FROM dbo.Product ORDER BY ' + @sortOrder + ' ' + @sortDir;

EXEC sp_executesql @sql;

Another plus for dynamic SQL, in spite of all the fear-mongering that is spread about it: you can get the best plan for each sort variation, instead of one single plan that will optimize to whatever sort variation you happened to use first. It also performed best universally in a recent performance comparison I ran:

http://sqlperformance.com/conditional-order-by

But why does this work, syntax-wise? The DESC is after END - that is, outside the CASE statement - so shouldn't it be applied regardless of the case? — SlimShaggy, Dec 17 '13 at 22:13
@SlimShaggy It's applying descending order to whatever comes out of the `CASE` expression. — Aaron Bertrand, Dec 17 '13 at 22:18
I second @SlimShaggy's question. I know it works because I've used it, but it definitely seems like there's some magic happening here. Say my sort params are 'created_date' and 'desc', then the resulting order by looks something like: "ORDER BY NULL , NULL , NULL DESC, created_date DESC". Testing shows that that is invalid SQL. So it seems that the parser is being extra smart for us and producing: "ORDER BY created_date DESC". An explanation of that would be marvelous if anybody has one. — Sean, May 18 '17 at 17:59
@Sean Constant expressions just aren't allowed in `order by`. That applies to `order by null` equally as to `order by 'foo'`: neither work. But a column name/index or expression that _happens_ always to evaluate to a single value _is_. So, `order by ExpressionContainingNoRowsOrSomeNulls` is valid. If the column contains nothing, it doesn't affect order. If it contains `null`s, they're sorted first, as an implementation decision by MS: https://dba.stackexchange.com/questions/8504/why-are-nulls-sorted-first. So, `order by ExpressionReturningOnlyNulls` acts as a no-op, and the later sorts win out — underscore_d, Jun 26 '17 at 09:55

score 23 · Answer 2 · answered Mar 25 '13 at 18:08

You need a case statement, although I would use multiple case statements:

order by (case when @sortOrder = 'name' and @sortDir = 'asc' then name end)  asc,
         (case when @sortOrder = 'name' and @sortDir = 'desc' then name end) desc,
         (case when @sortOrder = 'created_date' and @sortDir = 'asc' then created_date end) asc,
         (case when @sortOrder = 'created_date' and @sortDir = 'desc' then created_date end) desc

Having four different clauses eliminates the problem of converting between types.

What if you want to order by two columns? Is this possible with CASE statements? https://stackoverflow.com/questions/47388230/order-by-two-columns-with-usage-of-case-when/47388327#47388327 — FrenkyB, Nov 20 '17 at 09:16

score 4 · Answer 3 · answered Mar 25 '13 at 18:10

There are multiple ways of doing this. One way would be:

SELECT *
FROM
(
  SELECT
  ROW_NUMBER() OVER ( ORDER BY
  CASE WHEN @sortOrder = 'name' and @sortDir = 'asc' then name
  END ASC,
  CASE WHEN @sortOrder = 'name' and @sortDir = 'desc' THEN name
  END DESC,
  CASE WHEN i(@sortOrder = 'created_date' and @sortDir = 'asc' THEN created_date
  END ASC,
  CASE WHEN i(@sortOrder = 'created_date' and @sortDir = 'desc' THEN created_date
  END ASC) RowNum
  *
)
order by 
RowNum

You can also do it using dynamic sql.

score 1 · Answer 4 · edited Jun 22 '16 at 00:57

1

declare @str varchar(max)
set @str = 'select * from Product order by ' + @sortOrder + ' ' + @sortDir
exec(@str)

edited Jun 22 '16 at 00:57

Laurel

5,965
14
31
57

answered Mar 25 '13 at 18:08

ljh

2,546
1
14
20

T-SQL Conditional Order By

4 Answers4

Linked

Related