This article is as per request from Application Development Team Leader of my company. His team encountered code where application was preparing string for ORDER BY clause of SELECT statement. Application was passing this string as variable to Stored Procedure (SP) and SP was using EXEC to execute the SQL string. This is not good for performance as Stored Procedure has to recompile every time due to EXEC. sp_executesql can do the same task but still not the best performance.
Previously:
Application:
Nesting logic to prepare variable OrderBy.
Database:
Stored Procedure takes variable OrderBy as input parameter.
SP uses EXEC (or sp_executesql) to execute dynamically build SQL.
This was taking big hit on performance. The issue was how to improve the performance as well as remove the logic of preparing OrderBy from application. The solution I came up was using multiple CASE statement. This solution is listed here in simple version using AdventureWorks sample database. Another challenge was to order by direction of ascending or descending direction. The solution of that issue is also displayed in following example. Test the example with different options for @OrderBy and @OrderByDirection.
Currently:
Database only solution:
USE AdventureWorks
GO
DECLARE @OrderBy VARCHAR(10)
DECLARE @OrderByDirection VARCHAR(1)
SET @OrderBy = 'State' ----Other options Postal for PostalCode,
---- State for StateProvinceID, City for City
SET @OrderByDirection = 'D' ----Other options A for ascending,
---- D for descending
SELECT AddressID, City, StateProvinceID, PostalCode
FROM person.address
WHERE AddressID < 100
ORDER BY
CASE WHEN @OrderBy = 'Postal'
AND @OrderByDirection = 'D'
THEN PostalCode END DESC,
CASE WHEN @OrderBy = 'Postal'
AND @OrderByDirection != 'D'
THEN PostalCode END,
CASE WHEN @OrderBy = 'State'
AND @OrderByDirection = 'D'
THEN StateProvinceID END DESC,
CASE WHEN @OrderBy = 'State'
AND @OrderByDirection != 'D'
THEN StateProvinceID END,
CASE WHEN @OrderBy = 'City'
AND @OrderByDirection = 'D'
THEN City END DESC,
CASE WHEN @OrderBy = 'City'
AND @OrderByDirection != 'D'
THEN City END
GO
Above modified query has improved performance for our stored procedure very much. Let me know if you are implementing similar logic using any other method.
Reference : Pinal Dave (https://blog.sqlauthority.com)
126 Comments. Leave new
Thanks so much for the advice, I’m big fan of your page it had saved my day many times, regards
Hello to all i’m usin this code
select * from ARTICULOS
order by CD_ARTICULO asc
but the result that i have is 1,10,100,125 when the select result must be 1,2,3…10….100,etc why the clause is listing the ids not secuencialy 1,23??
it would be so much apreciated your help.
Thank to you very much =)
First you should never use “Select * “. What is the datatype of the column used in the order by clause?
thank you Carl for your time, the data type of the ID is varchar but actually we are using correlatives to introduce de ids.
and i use “select * ” because i need all the info not only the ID
thanks again =)
Why are you using a varchar for ID field?
Hi Carl thank you so much 4 your help, actually i’m a jr programer so i’m working in a database who other guys did and they decided to set varchar the type of the product id.
i guess this is why it orders 1,10,100 not sequentially, but exist one way to order the ids in sequentially way? or because they’re numbers and are set like char it cannot do??
thnks alot!
I always come across your posts. Thanks for the great information. Very helpful.
Thanks carl; You wrote this 2 years ago and it helped me today. It works greate ORDER BY
CASE
WHEN @col=”name” AND @sort=”asc” THEN (RANK() OVER (ORDER BY Name, Status))
WHEN @col=”name” AND @sort=”desc” THEN (RANK() OVER (ORDER BY Name DESC, Status))
WHEN @col=”status” AND @sort=”asc” THEN (RANK() OVER (ORDER BY Status, Name))
WHEN @col=”status” AND @sort=”desc” THEN (RANK() OVER (ORDER BY Status DESC, Name))
WHEN @col=”” THEN (RANK() OVER (ORDER BY Name, Status))
END
I have a bit of a different spin on this. I have a stored procedure that creates a temp table and does a bunch of code to populate it.
declare @data table (fields…)
much code…
select * from @data
order by sort1, sort2, sort3, sort4, sort5, sort6
I always want to sort by sort1-sort6
BUT each of these can be in ASC or DESC order. There are also cooresponding fields sort1direction – sort6direction what hold ‘ASC’ or ‘DESC’
So what I would need is something like this – but I know this doesn’t work.
select * from @data
order by sort1 sort1direction, sort2 sort2direction, sort3 sort3direction….
NOTE: I can’t use dynamic sql there is too much code in the body of this procedure and there are already several parts using dynamic sql statements.
Many Thanks!
LT
hi,
i have a problem as below
a table has 4 column and 1 row..I want only 1 record as a result by applying formula.
table is like
CCY POLID amt1 amt2 amt3 amt4
GBP 01XYZ 0.00 1.00 0.00 2.00
formula is if amt2 >0 then result =amt2
else if amt1 >0 then result =amt1
else if amt3>0 then result=amt3
else result =amt4
i need to insert the result value into a temp table also.
can you please help me to find a solution of this?
One answer to Simon’s good question as to why sort on the database side vs sorting in the application is that the database engine can do sorting using indexes (if the indexes exist). If the db engine can query straight from an index (which sometimes it can), then there is sometimes immense performance gain.
Someone in another website mentioned that the optimizer might get confused as to which saved execution plan to use and suggested building dynamic SQL in the SP, and sp_executesql (using parameterized query of course) so that the different execution plans get saved. Of course, in that case, one might also consider writing 6 different SPs each using a different order by. Not saying either one of those is best.
Hi Pinal , fantastic blog.
What if you have to order by more than 1 field? With the first field having ASC or DESC
This: (But the correct method, cannot seems to get it right)
ORDER BY
CASE WHEN @OrderBy = ‘Postal3’
THEN PostalCode END DESC, Street
CASE WHEN @OrderBy = ‘Postal2’
THEN PostalCode END ASC, Street
GO
Lien, Look at the above posts for Rank() Over.
Thanks Pinal
U R Star sir :) Tx..
if give this statement is not working helpme
order by table . column, case when @sortby =1 then vendor. vend_num
when @sortby=2 then vender_all.vend_category Else vendor_st.name
this statement if give 1 mean vend-num it come orderby depending upon the value order by is work but it was not work tell me the ans
What did you mean by “It was not work?”
Hi,
I got an error when I am opening management studio 2012. It is saying ‘can not find one or more components.Please reinstall the application’.What could happen.I already uninstall and reinstall sql server few times.But it is giving same error every time .Any solution would be appriciated.
Thanks
Very nice! I am using a CASE with 10 options for the ORDER BY. This would’ve been a super long query without using a case. Thanks!
Hy Dave. Thanks for all the great work you are doing. I’ facing a problem with order by case.
I am trying to select from one table a list of products ordered by price, year, name, and others….
The problem is that I must make zero values come last when sorting ascending.
SELECT * FROM Product P
ORDER BY CASE WHEN @OrderBy='Date ASC' THEN Date END ASC,
CASE WHEN @OrderBy='Price ASC' THEN Price END ASC,
CASE WHEN @OrderBy='Title ASC' THEN Title END ASC,
CASE WHEN @OrderBy='' THEN Match END
This works but don’t put the zero at the bottom of the list.
So, I tried to transform it (see next code), but it gave me the error ‘Incorrect syntax near ‘,’.’
select Price from Advert A
ORDER BY CASE WHEN @OrderBy='Price ASC' THEN
(case A.Price WHEN
0 THEN 1 ELSE 0
END,A.Price )
END ASC
I’ve seen this which was fast and simple:
SELECT *,
ROW_NUMBER( ) OVER (ORDER BY CASE @OrderBy
WHEN ‘param1’ THEN param1
WHEN ‘param2’ THEN param2
WHEN ‘param3’ THEN param3
ELSE ID
) as RowNo
FROM ….
…………..
If you wanted to, you could add an ORDER BY RowNo, but I believe that is unnecessary.
Following code is not giving any error but also not giving proper output.
DECLARE @SortField varchar(100)
set @SortField = ‘Make,Model’
SELECT Make,Model FROM CarMaster
WHERE ISNULL(DeletedFlag,0)=0
ORDER BY
Case @SortField When ” Then MAKE Else @SortField End
It is not possible. You need to use Dynamic SQL
what about I wanted the last one like this
CASE WHEN @OrderBy = ”
AND @OrderByDirection != ‘D’
THEN City, LastName, FirstName END
i cant seem to do it. any tips?
Hi Pinal,
I used a very simple method for my scenario. I have the sorting criteria set in a separate table.
My solution is like this:
DECLARE @LIST VARCHAR(4000)
SET @LIST = SUBSTRING(
( SELECT ‘, ‘ + CASE pfr.PRIORITY_CD WHEN ‘ACCT’ THEN ‘Acct’ ELSE ‘PlayerID’ END + CASE pfr.IS_ASC WHEN 1 THEN ‘ ASC’ ELSE ‘ DESC’ END
FROM dbo.PRIORITY_COMP_SETTING pcs WITH(NOLOCK)
JOIN dbo.PRIORITY_FACTOR_REF pfr WITH(NOLOCK) ON pcs.PRIORITY_ID = pfr.PRIORITY_ID
AND pfr.IS_ACTIVE = 1
WHERE pcs.COMP_SET_ID = 1
ORDER BY pcs.PRIORITY_IND
FOR XML PATH (”)
),2,20000)
–PRINT @LIST
SELECT *
FROM dbo.players
ORDER BY ( SELECT @LIST)
The only thing to keep in mind is to specify column names in single quotes in case you are directly writing the logic in order by clause instead of using a varchar type variable. I compared two methods and found that cost of using a varchar type variable is lower.
I suppose this method should apply to your example in blog and other examples in comments section.
Guys, please let me know whether this approach works for you. Please let me know your suggestions regarding performance as well.
Thanks,
Deepak Khandelwal
Guys, sorry, its not working, i have to use dynamic sql.
Gracias !