25 January 2009 - 5:42 PM / by Dominic Pettifer. 4 Comments for 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.
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 etcHowever, 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.
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
ENDAscending 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.
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 ASCRemember to provide a default Order By for when none of the CASE statements match.
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
Fantastic! Thanks a lot.
Posted on 19 April 2010 - 4:14 PM / by G
Gr888888888.. Thanks a lot as you solved my problem for Multiple Columns with Different DataTypes
Posted on 8 October 2009 - 7:49 AM / by Vedavati
I noticed that a simple SQL statement (one without a case in the order by) containing an order by, by a field that has an index created, uses the index also for the order by ( not just for the where ), but including a case in the order by, makes the engine not to use that index for the sorting, any ideas on how to make the engine use the index again in those cases?. I tried with the hints but no luck.
Posted on 6 July 2009 - 6:28 PM / by Guillermo
In email conversations, Guillermo has pointed out that using this method for dynamic Order By is very slow compared with directly appending the ORDER BY clause on manually, but only when the Order By for for an indexed column such as the PK ID. For instance, the following:
DECLARE @SortField varchar(50) SET @SortField = 'id' SELECT TOP 100 * FROM Photos ORDER BY CASE WHEN @SortField = 'id' THEN Id END
Against a table with 100'000 rows took 250ms, whereas:
SELECT TOP 100 * FROM Photos ORDER By Id
On the same recordset, only took 5ms. Using any other column, then the performance is identical, so it seems it's somehow not using the PK index for the dynamic order by.
I'm not sure of a solution to this, does anyone have any suggestions?
A non-Stored Procedure solution would be to use an ORM (Object Relational Mapper) such as LINQ 2 SQL, or NHibernate, as these generally output quite efficient T-SQL.
Posted on 6 July 2009 - 10:16 PM / by Dominic Pettifer (Administrator)
Red Bull gives you wings....that generate huge amounts of downforce #F1
about 18 hours ago from Twitterrific.vampire { -webkit-box-shadow: none; -webkit-box-reflection: none; } #cssjokes
7:44 PM July 30th from Echofon@edhenderson lol, lets get a trending topic going - .gangster .wrapper { color: #000; width: 150%; text-decoration: bling; } #cssjokes
7:36 PM July 30th from Echofon@weblivz I think the petition should be resubmitted but with security stuff taken out, as that's what the response purely focused on
6:13 PM July 30th from Echofon@weblivz I still think Chrome Frame can come to the rescue here, still keep their old browsers + legacy systems, no retraining costs etc.
6:12 PM July 30th from Echofon