http://www.dominicpettifer.co.uk/Blog/21/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’s ROW_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
)