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

  • Deepak Khandelwal
    May 20, 2014 7:59 pm

    Guys, sorry, its not working, i have to use dynamic sql.

    Reply
  • Gracias !

    Reply
  • Hi Mr.Pinal Dave,

    We have table containing millions of records.
    We are using paging concept to load 10 records from db and grid have sorting and filter facility also.

    For sorting i have used same method as you mention in this blog with cases. And it take around 3-4 second to load records

    But if i put fixed order by with direction then it will not 1 second so can you please help me to optimize my query.

    Thanks

    Reply
  • You can see how CASE expression can be effectively used in many cases

    Reply
  • when using this sorting sql engine is utilising 93% time for sorting.i also used option(recompile).but stil taking 23 seconds to complete execurion
    ROW_NUMBER() OVER (ORDER BY
    CASE WHEN @SortExpression = ‘ColumName ASc THEN SenderPublicIP END ASC

    Reply
  • thanks

    Reply
  • I have following problem :
    write a query to list the names of the owners in alphabetical order,
    when two owners have the same last name, order by first name.

    Do you think the query I wrote is right??

    SELECT o1.olname, o1.ofname
    FROM owner o1 JOIN owner o2
    GROUP BY o1.olname
    ORDER BY
    (CASE
    WHEN o1.olname o2.olname THEN o1.olname
    WHEN o1.olname = o2.olname THEN o1.ofname
    END);

    Reply
  • Why does this fail… and how can I fix it?

    > CASE WHEN (myNum > 5) THEN DueOn ASC ELSE SavedOn DESC END

    The DESC and ASC aren’t allowed. Why?

    Reply

Leave a Reply

Menu