Data Sorting with Dynamic/Conditional ORDER BY Statements

Almost in every web and windows application, users need to view data sort by various parameters. This is a very common scenario but there are many (yeah, too many) ways of solving this.

Some developers prefer to sort items within in the application code, some in database.  The correct decision depends on the nature of the problem.

I thought to demonstrate dynamic data sorting logic implemented in database side in this post. First of all, imagine we have to implement following business scenario:

Products are stored in AdventureWorks [Production].[Product] table. We need to sort products as below:

Strategy:

I am going to use dynamics ORDER BY functionality in SQL Server.

Steps:
Define a C# Enum to store the sorting options:

public enum SortBy {

    NameAtoZ        = 0,

    NameZtoA        = 1,

    PriceHighttoLow = 2,

    PriceLowtoHigh=3,

    SellStartDateHightoLow   = 4,

    SellStartDateLowtoHigh   = 5,

}

Build the drop down list to view sort options by assigning values of SortBy enum as values:

<asp:DropDownList ID="ddlProducts" runat="server">

   <asp:ListItem Text="Name (A-Z)" Value="0"></asp:ListItem>

   <asp:ListItem Text="Name (Z-A)" Value="1"></asp:ListItem>

   <asp:ListItem Text="Price (Low-High)" Value="2"></asp:ListItem>

   <asp:ListItem Text="Price (High-Low)" Value="3"></asp:ListItem>

   <asp:ListItem Text="Sell Start Date (High-Low)" Value="4"></asp:ListItem>

   <asp:ListItem Text="Sell Start Date (Low-High)" Value="5"></asp:ListItem>

</asp:DropDownList>

Pass the selected value of the drop down to the stored procedure. In the stored procedure, use dynamic ORDER BY clause to perform sorting:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE uspGetProductsSortBy

(

      @SortBy Int

)

AS

BEGIN

      
SELECT Name, ListPrice, SellStartDate

From Production.Product

ORDER BY         

   Case When @SortBy=0 Then Name END ASC,

   CASE When @SortBy=1 Then Name  END DESC,

   CASE When @SortBy=2 Then ListPrice END ASC,

   CASE When @SortBy=3 Then ListPrice END DESC,

   CASE When @SortBy=4 Then SellStartDate END ASC,

   CASE When @SortBy=5 Then SellStartDate END DESC   

   --Need to convert dates to string, otherwise result is not 100% correct

END

GO

References:

• http://www.sqlteam.com/article/dynamic-order-by
• http://msdn.microsoft.com/en-us/library/ms188385.aspx