SQL SERVER – CASE Statement in ORDER BY Clause – ORDER BY using Variable

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.

Solarwinds

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)

Solarwinds
,
Previous Post
SQL SERVER – Microsoft White Papers – Analysis Services Query Best Practices – Partial Database Availability
Next Post
SQL SERVER – Restore Database Without or With Backup – Everything About Restore and Backup

Related Posts

126 Comments. Leave new

  • Can i use the field selected in case statement? Need urgent info. Thanks

    SELECT DISTINCT PARTNERK1.job_no as jobNo, PARTNERK1.name, SendFlatK1.Date_send,
    CASE WHEN RESPONSE.AA_REGNO = ‘CUSERR’
    THEN (SELECT AA_REGNO FROM RESP WHERE RES_TYPE=’961′ AND JOB_NO = PARTNERK1.job_no)
    END [AA_REGNO],

    CASE WHEN RESPSTT.RES_CODE = ‘HD1’
    THEN ‘1ST REMINDER FOR HANGING CUSTOMS FORMS’
    WHEN RESPSTT.RES_CODE = ‘HD2’
    THEN ‘2ND REMINDER FOR HANGING CUSTOMS FORMS’
    –ELSE ‘CANCELLATION OF HANGING CUSTOMS FORMS’
    END [RES_CODE],
    Response.Recv_date,
    Response.Recv_time
    –FROM PARTNERK1 INNER JOIN (SendFlatK1 INNER JOIN (Response INNER JOIN RESPSTT ON RESPONSE.Recv_SNRF = RESPSTT.Recv_SNRF)
    FROM PARTNERK1 INNER JOIN (SendFlatK1 INNER JOIN
    (Response INNER JOIN RESPSTT ON RESPONSE.Recv_SNRF = RESPSTT.Recv_SNRF)
    ON SendFlatK1.ACCOUNT_NO = Response.ACCOUNT_NO AND SendFlatK1.job_no = Response.job_no)
    ON PARTNERK1.account_no = SendFlatK1.account_no AND PARTNERK1.job_no = SendFlatK1.job_no
    WHERE Response.Decl_Type = ‘K1’
    AND PARTNERK1.type = ‘1’
    AND Response.account_no = ‘177974’
    AND Response.user_id = ‘rat’
    AND Response.agent_code = ‘HF1001’
    AND Response.Res_Type = ‘963’
    AND Response.Status = ‘HD’
    AND RESPSTT.RES_CODE IN (‘HD1′,’HD2’)

    Reply
  • Sir please give a solution/query for the below table format

    hcode tcode datetime count

    0001 301 01.01.2011 12:23:34 6

    0001 301 01.01.2011 12:25:45 8

    0002 301 01.01.2011 12:25:49 8

    002 301 01.01.2011 12:25:55 7

    so from the above table i want to show like below

    hcode tcode datetime count

    0001 301 01.01.201 14
    0002 301 01.01.2011 15

    where i have used count function and convert function to select date between two date to get the result as the 1st table..but i need to show like the second one..

    please reply quickly…

    Reply
  • Thanks so much for the advice, I’m big fan of your page it had saved my day many times, regards

    Reply
  • 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 =)

    Reply
    • First you should never use “Select * “. What is the datatype of the column used in the order by clause?

      Reply
      • 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?

    Reply
    • 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!

      Reply
  • I always come across your posts. Thanks for the great information. Very helpful.

    Reply
  • 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

    Reply
    • 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

      Reply
  • 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?

    Reply
  • Henry B. Stinson
    September 12, 2012 12:13 am

    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.

    Reply
  • 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

    Reply
  • jayendrasinh gohil
    May 9, 2013 4:30 pm

    Thanks Pinal

    Reply
  • U R Star sir :) Tx..

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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!

    Reply
  • 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

    Reply
  • 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.

    Reply
  • 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

    Reply
  • 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?

    Reply
  • Deepak Khandelwal
    May 20, 2014 6:57 pm

    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

    Reply

Leave a Reply

Menu