SQL – Using CASE with ORDER BY in Stored Procedures

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! 🙂