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
Pinal,
This post saved my skin. You continue to amaze and satisfy with your knowledge of SQL Server.
Pinal,
Is it possible to give an OrderBy like this below..
Order By
case Column1
when ‘IT1’ then ‘XXX’
else Column1
end
I suppose then part should carry a column name for an order by clause.
has anybody figured out to get around the type problem.
i know you can CAST things over to the same type, but when you cast an int over to a varchar is does whole string order. 1,11,111,2,22,222,3… I saw something mentioned about padding? i need padding on my head, but i am sure that is not what they meant.
my types are
3 varchars
2 dates
2 int
i stinks to cast all to varchars.
thank you in advance!
Pinal.
I’m wondering if I can order by more than one column using the technique you have discussed here.
Here is an example of what I want to accomplish.
..
..
(ORDER BY
CASE @SortOrder
WHEN ‘asc’ THEN
CASE @SortBy
WHEN ‘This’ THEN
CASE [That]
WHEN ‘A’ THEN 3
WHEN ‘B’ THEN 2
WHEN ‘C’ THEN 1
ELSE 0
END, [This] –the comma doesn’t compile
END
END asc
Basically what I want to do is, when the user wants to sort by [This], I want to sort by [That] first using my custom specifications then sort by [This] normally.
If I just wanted to sort by [This], I would code
WHEN ‘This’ THEN [This]
But in my case I want to do some custom logic on [That] first then sort by [This]. The comma syntax I present there doesn’t work.
I’d like to avoid having to put another case statement for each WHEN clause in the CASE [That] clause if possibke.
Just wondering if you had any suggestions, thanks!
Matt.
Aha. I should have gone through the comments first!
I see Rökkvi has answered my question. I am eager to try!
Cheers folks.
matt.
I’m wondering if anyone can help me with a cleaner implementation of this.
–note if sorting by [this] we need to sort by [that] first then by [this]
case @SortOrder
when ‘asc’ then
case @SortBy
when ‘This’ then
case [That]
when 4 then 1
else 0
end
end
end desc,
case @SortOrder
when ‘asc’ then
case @SortBy
when ‘This’ then [This]
end
end desc,
I think that that should evaluate to
Order by [That], [This]
Cheers folks.
Matt
This articles really helped me a lot….This solution fixed my problem….Thanks
I used above solution as below…
ORDER BY
CASE WHEN @sort = 0
THEN X.SurName + ‘ ‘ + X.FirstName END,
CASE WHEN @sort = 1
AND @sortdirection = ‘D’
THEN X.ClientName END DESC,
CASE WHEN @sort = 1
AND @sortdirection = ”
THEN X.ClientName END,
CASE WHEN @sort = 2
AND @sortdirection = ‘D’
THEN X.StatusTypeDesc END DESC,
CASE WHEN @sort = 2
AND @sortdirection = ”
THEN X.StatusTypeDesc END,
CASE WHEN @sort = 3
AND @sortdirection = ‘D’
THEN Convert(varchar(20),X.CreationDate) END DESC,
CASE WHEN @sort = 3
AND @sortdirection = ”
THEN Convert(varchar(20),X.CreationDate) END,
CASE WHEN @sort = 4
AND @sortdirection = ‘D’
THEN X.Name END DESC,
CASE WHEN @sort = 4
AND @sortdirection = ”
THEN X.Name END,
CASE WHEN @sort = 5
AND @sortdirection = ‘D’
THEN Convert(varchar(20),X.StartDate) END DESC,
CASE WHEN @sort = 5
AND @sortdirection = ”
THEN Convert(varchar(20),X.StartDate) END,
CASE WHEN @sort = 6
AND @sortdirection = ‘D’
THEN X.categorycode END DESC,
CASE WHEN @sort = 6
AND @sortdirection = ”
THEN X.categorycode END
I’m a bit rust on SQL but this statement in the order by confuses me. Doesn’t 3 mean the 3rd column, 2 mean the 2nd, etc…? If so why doesn’t 0 throw an error? I know it doesn’t but I don’t know why.
CASE [That]
WHEN ‘A’ THEN 3
WHEN ‘B’ THEN 2
WHEN ‘C’ THEN 1
ELSE 0
Nvm, I just remembered that SQL treats it as a value when coming out of a CASE WHEN.
Thank you Vassalis for answering the question and Rökkvi for providing the answer!
SET NOCOUNT ON
IF LEN(@SortExpression) = 0
SET @SortExpression = ‘ID DESC’
SELECT * FROM
(
SELECT
[ID],
[AddedBy],
[AddedDate],
[AmountOfSales],
[ModifiedBy],
[ModifiedDate],
[…],
ROW_NUMBER() OVER (ORDER BY CAST(@SortExpression as sql_variant)) AS RowNum
FROM
[dbo].[sell_Sales]
WHERE
([UserID] = @UserID)
) sales
WHERE sales.RowNum BETWEEN (@PageIndex*@PageSize+1) AND ((@PageIndex+1)*@PageSize)
ORDER BY CAST(@SortExpression as sql_variant)
I have to List all the coutries from counties table order by CountryName, but I want United states and Germany to be on the top of list.
@Puneet,
This is usually done through Front End. They can put a default value in the combo box (drop down box). Ask your Front End Developer to implement this logic through front end.
I have seen many cases, we usually implement these things through front end.
~ IM.
Hi Pinal, works great, thx!
Puneet, you can use this trick:
ORDER BY
CASE WHEN Country=’US’ OR Country=’UK’ THEN 0
ELSE 1 END
, Country
This will order the list at first by 1 for us and uk and 0 for others then reorder it by country for those with same 1 or 0
I have on urgent query….
suppose i am using like query to search…
query may be like
select firstname from tbllogin where firstname like ‘%su%’
what i want how can i sort data on the basis of result found and then sorted on searched value i.e “su”
return data will be like
firstname
sumit
sumitkumar
kumarSumit
vermakumarsumit
i hope you get me….
please help me
@Sumit,
Use CharIndex function to location the position of ‘SU’ Character. Below is a sample code.
create table #tbllogin (firstname varchar(200))
go
insert into #tbllogin values (‘Kumarsumit’)
insert into #tbllogin values (‘sumit’)
insert into #tbllogin values (‘Kumsumit’)
insert into #tbllogin values (‘Ssumit’)
go
Select FirstName from #tbllogin
go
Select FirstName
from #tbllogin
Where FirstName like ‘%SU%’
order by charindex ( ‘su’, firstname )
go
drop table #tbllogin
~ IM.
I agree with Imran Mohammed also you can add the length of the firstname as I noticed you wand the shorter firstnames first:
Select FirstName
from #tbllogin
Where FirstName like ‘%SU%’
order by charindex ( ’su’, firstname ),Len(firstname)
Thanks a lot, i was stuck with this problem
your code help me :D
i going to buy some beers for you :P
your blog save me a lot of times