• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

SQL – Using CASE with ORDER BY in Stored Procedures

May 31, 2015 by Andy Hayes 2 Comments

When writing SQL it is possible to use ORDER BY with CASE conditions to help provide more flexibility over the returned order of a record set and this is especially useful when writing stored procedures which require a dynamic ORDER BY clause. This method does not require any dynamic SQL build up strings executed either using EXEC or sp_executeSQL

Lets take this example using the AdventureWorks database that is downloadable for SQL Server.

This procedure which is designed to accept three parameters which will help return records for a sales person  and in a specified column order and in a particular sort order.

CREATE PROCEDURE usp_GetOrders @SalesPersonID INT, @OrderBy VARCHAR(20), @SortOrder CHAR(4)
AS
SELECT SalesOrderNumber, PurchaseOrderNumber, TotalDue, OrderDate, DueDate, AccountNumber  
FROM Sales.SalesOrderHeader
WHERE SalesPersonID = @SalesPersonID
ORDER BY 
  CASE WHEN @OrderBy = 'Due Date' AND @SortOrder = 'DESC' THEN DueDate END DESC,
  CASE WHEN @OrderBy = 'Due Date' THEN DueDate END,
  CASE WHEN @OrderBy = 'Order Date' AND @SortOrder = 'DESC' THEN OrderDate END DESC,
  CASE WHEN @OrderBy = 'Order Date' THEN OrderDate END,
  CASE WHEN @OrderBy = 'Total Due' AND @SortOrder = 'DESC' THEN TotalDue END DESC,
  CASE WHEN @OrderBy = 'Total Due' THEN TotalDue END

One thing you might notice, I have supplied the DESC version of the CASE first for each column. There are two reasons for this; the default sort order if often ASC so I have not supplied an ASC for second CASE for each column but in doing this (the second reason), the CASE will not work correctly if the DESC is the second line as the first line will always return true.

So this:

CASE WHEN @OrderBy = 'Due Date' THEN DueDate END,  
CASE WHEN @OrderBy = 'Due Date' AND @SortOrder = 'DESC' THEN DueDate END DESC,

Will always return the data in ASC order regardless of whether I supply DESC in @SortOrder. In this case either make DESC the first line as written or re-write as:

CASE WHEN @OrderBy = 'Due Date' AND @SortOrder = 'ASC' THEN DueDate END,  
CASE WHEN @OrderBy = 'Due Date' AND @SortOrder = 'DESC' THEN DueDate END DESC,

I hope you found this useful. If you know of better ways to achieve this, please leave a comment. I’d love to hear from you! 🙂

Related Posts:

  • How to fix "conversion failed when converting date and/or time from character string"
    How to fix "conversion failed when converting date…
  • sql grouping sets
    Using SQL GROUPING SETS for Multiple GROUP BY…

Filed Under: All Articles, SQL Tips and Tricks Tagged With: sql server, t-sql

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Reader Interactions

Comments

  1. Flater says

    June 26, 2018 at 12:26 pm

    I’m not quite sure why you put the ASC after the DESC. I understand the order of operations and why you can omit the asc/desc check for the second line, but shy not simply put “AND @SortOrder = ‘ASC'” on the first line and then omit the DESC check?

    I know either works, I’m just curious as to why you chose the least expected option 🙂

    Reply
  2. Jay says

    September 4, 2018 at 9:03 pm

    Wow, this is a crazy cool solution. I’m not even sure why this works since most of the CASE statements will return NULL, but it does.

    This should eliminate 99{96f5cbaad583fb885ff5d18ecce5734bf1e8596949521e20c290401929518f75} of the excuses for EXEC @sql.

    Thank you for sharing.

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Categories

  • All Articles (84)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (21)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to fix “conversion failed when converting date and/or time from character string”

Recent Posts

  • How to fix “conversion failed when converting date and/or time from character string”
  • Using SQL GROUPING SETS for Multiple GROUP BY Queries in a Single Query
  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial

Search

Connect

  • Twitter
  • Facebook
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©