Dynamic/Conditional Order By Clause in SQL Server/T-SQL

Technical Article - This article shows you how to achieve a conditional Order By clause in your SQL Server Stored Procedures, without having to resort to inline SQL in your applications, or Dynamic SQL (yuck!) in your Stored Procedures.

Inline SQL or Stored Procedures

You've probably come across times when you needed to sort a recordset dynamically in a Stored Procedure based on an input parameter. For instance, if you're displaying records on a webpage with sortable columns, like on most ecommerce sites. You could use an inline SQL statement dynamically built in your ASP/PHP server-side code with string concatenation eg:

string sql = "SELECT * FROM Products ORDER BY ";

if(Request.QueryString["orderBy"] == "NameAsc")
{
    sql += "Name ASC";
}
else if(Request.QueryString["orderBy"] == "NameDesc")
{
    sql += "Name DESC";
}
else if(......etc etc

However, you're probably using a Stored Procedure for reasons such as performance (execution plan is cached in a Sproc), maintainability (string concatenation can get messy with complex queries), prevent security vulnerabilities (such as SQL Injection), and maybe you want to achieve efficient database tier paging using SQL Server 2005'sROW_NUMBER() function.

Introducing the CASE Function

The trick is to use the CASE function, but there are quirks with this that can trip you up. First of all you declare a Stored Procedure with an Order By input parameter and apply the CASE to the Order By clause like this:

CREATE PROCEDURE GetProducts
(
    @OrderBy      VARCHAR(50),
    @Input2       VARCHAR(30)
)
AS
BEGIN
    SET NOCOUNT ON

    SELECT Id, ProductName, Description, Price, Quantity
    FROM Products
    WHERE ProductName LIKE @Input2
    ORDER BY
        CASE            
            WHEN @OrderBy = 'ProductNameAsc' THEN ProductName
        END ASC,
        CASE
            WHEN @OrderBy = 'ProductNameDesc' THEN ProductName
        END DESC

END

Ascending and Descending actions need to be grouped into separate CASE statements, separated with a comma. In your server-side code/script make sure to append 'Asc' or 'Desc' onto the order by string, or you could have two Stored procedure input parameters for column name and order by direction if you want.

Multiple Columns with Different DataTypes

You'll hit problems when you try to include multiple columns with different data types (VARCHAR, INT etc.). Eg:

ORDER BY
CASE            
    WHEN @OrderBy = 'ProductNameAsc' THEN ProductName
    WHEN @OrderBy = 'QuantityAsc' THEN Quantity
END ASC

...will throw an error because ProductName is a VARCHAR and Quantity is an INT...

Conversion failed when converting the nvarchar value 'Value' to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation.

I've seen people wrap the column around a convert function eg.

WHEN @OrderBy = 'QuantityAsc' THEN CONVERT(VARCHAR(32), Quantity)

DON’T do this as the order by will be different as a string versus it's original data type (eg. 30 will come before 4 as the first character 3 is earlier in the alphebet then 4). This is especially true with DATETIME types as the CONVERT function could convert to into any number of date time formats (2009/01/15, 01/15/2009 etc.)

Instead you have to separate each datatype into separate groups of case statements like this:

SELECT Id, ProductName, Description, Price, Quantity
FROM Products
WHERE ProductName LIKE @Input2
ORDER BY
    CASE –- VARCHAR types ascending
        WHEN @OrderBy = 'ProductNameAsc' THEN ProductName
    END ASC,
    CASE –- VARCHAR types descending
        WHEN @OrderBy = 'ProductNameDesc' THEN ProductName
    END DESC,
    CASE -- INT types ascending
        WHEN @OrderBy = 'QuantityAsc' THEN Quantity
    END ASC,
    CASE -- INT types descending
        WHEN @OrderBy = 'QuantityDesc' THEN Quantity
    END DESC,
    CASE -- MONEY types ascending
         WHEN @OrderBy = 'PriceAsc' THEN Price
    END ASC,
    CASE -- MONEY types descending
        WHEN @OrderBy = 'PriceDesc' THEN Price
    END DESC,
    CASE -- Default order by
        WHEN 1 = 1 THEN ProductName
    END ASC

Remember to provide a default Order By for when none of the CASE statements match.

Paging With ROW_NUMBER()

You can also use this technique with database tier paging using the ROW_NUMBER() function in SQL Server 2005/2008:

SELECT
    Id,
    ProductName,
    Deacription,
    Quantity
FROM
(
    SELECT ROW_NUMBER() OVER(
        ORDER BY
            CASE
                WHEN @OrderBy = 'ProductNameAsc' THEN ProductName
            END ASC,
            CASE
                WHEN @OrderBy = 'ProductNameDesc' THEN ProductName
            END DESC,
            CASE
                WHEN 1 = 1 THEN ProductName
            END ASC,
            //-- Snip --//
        ) AS RowNumber,
        Id,
        ProductName,
        Description,
        Price,
        Quantity
    FROM Products
    WHERE ProductName LIKE @Input2
) i
WHERE RowNmber BETWEEN @FirstRecord AND @LastRecord
ORDER BY RowNumber ASC