SQL Ninja uses ORDER BY with CASE

Hi, today I want to show you simple technique which can be used when you need parameterised ORDER BY clause in T-SQL. Sometimes you may want to have an elegant query. An example is today’s code review that I gave to a colleague. On the Internet is very difficult to find such a solution. So, I decided to show it to you here. There are many possible scenarios for this example usage. For example, you may want to have in a stored procedure a nice simple query that depends on the sort order parameter. Another example of when you have to make it is using C, which can be complicated with several queries flow. With this technique, your T-SQL code will be much more compact and may run faster. It will be much simpler without separated queries for each ORDER BY. Enjoy!

use tempdb
go
-- prepare test context
if object_id('OrderByTest') is not null
  drop table OrderByTest
create table OrderByTest (
 Val1 VARCHAR(10),
 Val2 INT
)
insert into OrderByTest (Val1, Val2) values ('a', 2)
insert into OrderByTest (Val1, Val2) values ('b', 1)
-- I need those sort orders in 0 and 1 cases
-- 0
select * from OrderByTest order by Val1 ASC, Val2 DESC
-- 1
select * from OrderByTest order by Val1 DESC, Val2 DESC
declare @sort bit
set @sort = 0
select *
-- query with @sort param in order by
from OrderByTest
order by
    case when @sort = 0 then Val1 end ASC,
    case when @sort = 0 then Val2 end DESC,
    case when @sort = 1 then Val1 end DESC,
    case when @sort = 1 then Val2 end DESC
set @sort = 1
-- query with @sort param in order by
select *
from OrderByTest
order by
    case when @sort = 0 then Val1 end ASC,
    case when @sort = 0 then Val2 end DESC,
    case when @sort = 1 then Val1 end DESC,
    case when @sort = 1 then Val2 end DESC
-- clean up
if object_id('OrderByTest') is not null
  drop table OrderByTest

p ;).