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
It is very tricky technique, however I use it primarily in WHERE clause to filter data depending on whether user has delivered given value or not. I don’t know why so few developers use it while building T-SQL queries…
BTW. Cool blog, keep going!
The longer story of this is fetching rows into variable table and then there were 2 almost identical queries divided by if statement to select top 1 row depending on the order by. Thanks to this technique I advice my colleague was able to create this as CTE and get only once top 1 row with order by regulation from outside procedure parameter. And performance is much better because CTE with top 1 works much better and we could eliminate variable table as well. That gives 0 logical writes.
I like work on this blog, even with difficulties at the begin. I am happy you like it. I will keep going develop on this site for sure.