25 January 2009 - 5:42 PM / by Dominic Pettifer. 6 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
This is really gr8888888888888888888888888888888
I spend 4 hours searching, googling, jumping with tensions to solve ordering with different datatypes and ROW NUMBER and then I saw this article.
THANK YOU SO SO SO MUCH for saving my time.
Posted on 26 February 2011 - 3:14 PM / by waqas
I wanted to thank you for this great guide! This is a very informative post, it help me more. and i will share my website..
Posted on 31 December 2011 - 11:10 AM / by ezydvd
Excellent , solved many of my problems
Posted on 18 November 2010 - 12:52 PM / by RaviChandra P.
Thanks for sharing these useful information! Hope that you will continue doing nice article like this.
Posted on 30 December 2011 - 5:52 AM / by Screen printed t-shirts
An SEO Company USA hires the best employees for their organization, who have the ability to face the new challenges and accomplish the need of clients. Indian SEO companies offer high quality services at affordable rates as they find qualified resources easily. If you are seeking out instant result, hire a best SEO company London, who is fully experienced and has a good rapport in the online market. As soon as you hire an Indian company, it starts showing you result (WEB Development India).
Posted on 3 January 2012 - 12:54 PM / by WEB Development India
Fantastic! Thanks a lot.
Posted on 19 April 2010 - 4:14 PM / by G
quarter, moncler mens charm, Moncler <strong><a href="http://www.monclersjackor.net/moncler-barn-c-7.html" title="Moncler Coats">Moncler Coats</a></strong> Gamme Bleu series of wind <strong><a href="http://www.monclersjackor.net/moncler-barn-c-7.html" title="Moncler Kids">Moncler Kids</a></strong> hit the United <strong><a href="http://www.monclersjackor.net/moncler-coats-kvinnor-c-1.html" title="Moncler sverige">Moncler sverige</a></strong> States, with most standard <strong><a href="http://www.monclersjackor.net/moncler-barn-c-7.html" title="Moncler Vest">Moncler Vest</a></strong> American <strong><a href="http://www.monclersjackor.net/moncler-barn-c-7.html" title="Moncler Barn">Moncler Barn</a></strong> design style, different <strong><a href="http://www.monclersjackor.net/moncler-boots-c-9.html" title="Moncler Boots">Moncler Boots</a></strong> from the ...
Posted on 16 October 2011 - 4:48 AM / by Moncler jacka
thank you for providing such information, of course, want you to write more Some of these articles,
Posted on 25 October 2011 - 6:21 AM / by moncler down jackets
river Beidaying, but still no <strong><a href="http://www.nikefreerunskotilbud.com/nike-lunar-eclipse-dame-c-16.html" title="køb nike lunarglide">køb nike lunarglide</a></strong> help in <strong><a href="http://www.nikefreerunskotilbud.com/nike-free-70-dame-c-5.html" title="Nike Free Run Dame">Nike Free Run Dame</a></strong> the <strong><a href="http://www.nikefreerunskotilbud.com/nike-lunar-eclipse-dame-c-16.html" title="nike lunar tilbud">nike lunar tilbud</a></strong> strategic situation <strong><a href="http://www.nikefreerunskotilbud.com/nike-lunar-eclipse-dame-c-16.html" title="køb nike lunar">køb nike lunar</a></strong> after <strong><a href="http://www.nikefreerunskotilbud.com/nike-free-run-m%C3%A6nd-c-9.html" title="Nike Free DK">Nike Free DK</a></strong> the <strong><a href="http://www.nikefreerunskotilbud.com/nike-free-run-2-m%C3%A6nd-c-11.html" title="Nike Free Run 2">Nike Free Run 2</a></strong> fall of <strong><a href="http://www.nikefreerunskotilbud.com/" title="Nike Free Sko">Nike Free Sko</a></strong> Anqing, Taiping Rebellion was <strong><a href="http://www.nikefreerunskotilbud.com/nike-lunar-eclipse-dame-c-16.html" title="nike lunar sko">nike lunar sko</a></strong> finally...
Posted on 16 November 2011 - 5:51 AM / by Nike Free 3.0
[url=http://www.retrojordansstore.com/air-jordan-retro-1-c-26.html]Jordan Retro 1[/url] bout [url=http://www.retrojordansstore.com/air-jordan-retro-12-c-16.html]Jordan Retro 12[/url] 40 % [url=http://www.retrojordansstore.com/air-jordan-retro-1-c-26.html]air Jordan Retro 1[/url] of the shares
Posted on 29 December 2011 - 3:42 AM / by Jordan Retros
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
Low blood sugar sugar <i><a href=http://www.airjordansstore.net/air-jordan-retro-15-c-40.html>air jordan 15</a></i> sugar during the night is often a problem for type 1 diabetics.[The
has the potential to improve safety and efficacy of insulin delivery and and <i><a href=http://www.airjordansstore.net/>Jordan Shoes Store</a></i> and may in future allow more flexible lifestyles in conjunction with improved
control for people with type 1 diabetes, wrote the study authors, led led <b><a href=http://www.airjordansstore.net/air-jordan-retro-15-c-40.html>air jordan 15</a></b> led by Roman Hovorka of the University of Cambridge.One of the sticking
that could potentially delay the device's approval is the fact that continuous continuous <h3><a href=http://www.airjordansstore.net/air-jordan-retro-12-c-16.html>Air Jordan Retro 12</a></h3> continuous glucose monitors are not approved to provide insulin dosages, only to
a person that blood sugar is high or low.Today's insulin systems
Posted on 5 December 2011 - 7:40 AM / by Jordan Retros
<b><a href="http://www.canadagoose.com.co/">canada goose</a></b> e current housing Hainan no <b><a href="http://www.canadagoose.com.co/">canada goose outlet</a></b> <b><a href="http://www.canadagoose.com.co/">canada goose sale</a></b> independent safety assessment body registered in the provinces housin...
Posted on 29 January 2012 - 2:54 AM / by jordan 6
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)
stores, to a satisfactory selection of bags to stores, <a href="http://www.nikejordanoutlets.com/air-jordan-retro-6-C37.html" title="jordan 6 for sale">jordan 6 for sale</a> is not an easy task <br> <br> Oh. JM <br> <br> to help you in the purchase of bags, the need to spend too much <a href="http://www.nikejordanoutlets.com/" title="air jordan on sale">air jordan on sale</a> time and easier to enjoy online shopping <br> <br> process. Even according to my experience of the past two years, Taobao, combined...
Posted on 23 May 2011 - 6:51 AM / by jordan shoe wholesale
you leave act as direct to shop cache mademoiselle of <b><a href="http://www.louboutinshoesukstore.com">christian louboutin shoes</a></b> mind this Christmas. A Buyer’s inventory to Purchasing Designer Watches <b><a href="http://www.louboutinshoesukstore.com">discount christian louboutin shoes</a></b> you leave act as direct to shop cache mademoiselle of <b><a href="http://www.louboutinshoesuksaleuk.com">christian louboutin uk</a></b> mind this Christmas. A Buyer’s inventory to Purchasing Designer Watches <b><a href="http://www.louboutinshoesuksaleuk.com">christian louboutin online</a></b> you leave act as direct to shop cache mademoiselle of <b><a href="http://www.fashionishop.com">designer outlet</a></b> mind this Christmas. A Buyer’s inventory to Purchasing Designer Watches <b><a href="http://www.fashionishop.com">fine jewelry</a></b> you leave act as direct to shop cache mademoiselle of <b><a href="http://www.louivuitonhandbags.com">lv</a></b> mind this Christmas. A Buyer’s inventory to Purchasing Designer Watches <b><a href="http://www.louivuitonhandbags.com">louis vuitton handbag</a></b>
Posted on 8 June 2011 - 4:18 AM / by louboutin uk
[url=http://www.nikingshoes.com/]Nike Dunks Shoes[/url]
[url=http://www.nikingshoes.com/]Nike Dunks[/url]
[url=http://www.nikingshoes.com/]Nike Dunk Sb[/url]
[url=http://www.nikingshoes.com/]Cheap Nike Dunk[/url]
[url=http://www.nikingshoes.com/]Nike Dunks High[/url]
[url=http://www.nikingshoes.com/]Cheap Nike Shoes[/url]
[url=http://www.nikingshoes.com/blog/]Nike Dunks Shoes Blog[/url]
[url=http://www.nikingshoes.com/new.html]Nike Dunks Shoes News[/url]
[url=http://www.nikingshoes.com/big-nike-high-womens-c-7.html]Big Nike High Womens[/url]
[url=http://www.nikingshoes.com/black-nike-dunk-c-9.html]Black Nike Dunk[/url]
[url=http://www.nikingshoes.com/boys-nike-dunk-c-4.html]Boys Nike Dunk[/url]
[url=http://www.nikingshoes.com/nike-60-dunk-se-c-8.html]Nike 6.0 Dunk SE[/url]
[url=http://www.nikingshoes.com/nike-dunk-high-c-5.html]Nike Dunk High[/url]
[url=http://www.nikingshoes.com/nike-dunk-high-women-c-2.html]Nike Dunk High Women[/url]
[url=http://www.nikingshoes.com/nike-dunk-low-c-1.html]Nike Dunk Low[/url]
[url=http://www.nikingshoes.com/nike-dunk-low-women-c-3.html]Nike Dunk Low Women[/url]
[url=http://www.nikingshoes.com/nike-sb-dunk-mid-c-6.html]Nike SB Dunk Mid[/url]
[url=http://www.nikingshoes.com/red-nike-dunk-c-10.html]Red Nike Dunk[/url]
[url=http://www.nikingshoes.com/white-black-dunk-c-11.html]White Black Dunk[/url]
[url=http://www.nikingshoes.com/white-nike-dunk-c-12.html]White Nike Dunk[/url]
[url=http://www.nikingshoes.com/nike-dunk-low-c-1.html]nike dunk sb low[/url]
[url=http://www.nikingshoes.com/nike-dunk-low-c-1.html]nike dunk low pro sb[/url]
[url=http://www.nikingshoes.com/nike-dunk-low-c-1.html]nike dunk low sb[/url]
Posted on 9 June 2011 - 10:20 AM / by Nike Dunks Shoes
www.christianlouboutins-u.com
Posted on 9 June 2011 - 10:32 AM / by Christian Louboutin Shoes
Newest styles of <a href="http://www.christianlouboutinreplicacl.com/"><strong>christian louboutin high heels</strong></a> in hot sale now, <a href="http://www.christianlouboutinreplicacl.com/"><strong>Christian Louboutin Knockoffs</strong></a> shoes sale now, buy <a href="http://www.christianlouboutinreplicacl.com/"><strong>christian louboutin replica</strong></a> in our online uk store .your shoes sales prices will save.
Posted on 22 September 2011 - 2:35 AM / by christian louboutin replica
of <strong><a href="http://www.nikefreerunskotilbud.com/nike-free-50-dame-c-4.html" title="Nike Free Sort Lilla">Nike Free Sort Lilla</a></strong> friends <strong><a href="http://www.nikefreerunskotilbud.com/nike-free-50-dame-c-4.html" title="Nike Free Udsalg">Nike Free Udsalg</a></strong> in <strong><a href="http://www.nikefreerunskotilbud.com/nike-free-70-dame-c-5.html" title="Nike Free Run Sko">Nike Free Run Sko</a></strong> the <strong><a href="http://www.nikefreerunskotilbud.com/nike-free-50-dame-c-4.html" title="Nike Free 5.0">Nike Free 5.0</a></strong> inter-model, she Er <strong><a href="http://www.nikefreerunskotilbud.com/nike-free-30-dame-c-3.html" title="Nike Free 3.0">Nike Free 3.0</a></strong> Yi home, three uncle have a daughter in <strong><a href="http://www.nikefreerunskotilbud.com/nike-free-30-dame-c-3.html" title="Billig Nike Free">Billig Nike Free</a></strong> <strong><a href="http://www.nikefreerunskotilbud.com/" title="Nike Free Tilbud">Nike Free Tilbud</a></strong> high school, they usually teach childre...
Posted on 16 November 2011 - 6:08 AM / by Nike Free 3.0
<strong><a href=http://www.nikefreerunskotilbud.com/nike-free-30-dame-c-3.html title=Nike Free 3.0>Nike Free 3.0</a></strong> <strong><a href=http://www.nikefreerunskotilbud.com/ title=nike free run>nike free run</a></strong>
Posted on 16 November 2011 - 8:40 AM / by Nike Free 3.0
. However, the armed suspect, but <b><a href="http://www.christianlouboutinshoes.co/">christian louboutin on sale</a></b> said <b><a href="http://www.christianlouboutin.mobi/">christian louboutin sale</a></b> that <b><a href="http://www.christianlouboutinshoes.co/">christian louboutin shoes</a></b> he was <b><a href="http://www.christianlouboutin.mobi/">christian louboutin discount</a></b> in Kashgar Public Security <b><a href="http://www.christianlouboutinshoes.co/">louboutin shoes</a></b> Bureau refused <b><a href="http://www.christianlouboutin.mobi/">christian louboutin on sale</a></b> to p...
Posted on 29 January 2012 - 3:10 AM / by jordan 6
<b><a href="http://www.classic-sparkle-ugg-boots.com/">ugg sale uk</a></b> with <b><a href="http://www.euggsonsale.com/">uggs on sale</a></b> <b><a href="http://www.uggclassictallbootsforsale.com/">uggs boots sale</a></b> greetings. <b><a href="http://www.classic-sparkle-ugg-boots.com/">ugg australia sale</a></b> <br> <b><a href="http://www.uk-ugg-sale.co.uk/">ugg australia uk</a></b> <p> (Reporter <b><a href="http://www.usauggbootsclearance.com/">ugg boots clearance</a></b> Dan Chen Yi-Ping Xie <b><a href="http://www.eclassicshortuggboots.com/">ugg sale</a></b> Tao <b><a href="http://www.uggclassictallbootsforsale.com/">cheap ugg boots uk</a></b> Sun) <b><a href="http://www.uk-ugg-sale.co.uk/">ugg online</a></b> the <b><a href="http://www.eclassicshortuggboots.com/">ugg boots on sale uk</a></b> spe...
Posted on 15 October 2011 - 2:34 AM / by ugg boot clearance
ety supervision departments <b><a href="http://www.canadagoosejackets.com.co/">canada goose jacket</a></b> rushed to the scene to direct rescue. </P> <p> <b><a href="http://www.canadagoosejackets.com.co/">canadian goose jacket</a></b> but so far, <b><a href="http://www.canadagoosejackets.com.co/">canada goose jacket sale</a></b> why the ve...
Posted on 29 January 2012 - 1:52 AM / by cheap jordan shoes
And YouTube still auto-fucking-plays videos!! This is TWO-THOUSAND-AND-FUCKING-TWELVE FFS!!!
about 20 hours ago from webOn a side-note, YouTube's commenting system is god-awful atrocious dreadful horrible horrible horrible!! Constant meaningless error messages
about 20 hours ago from webJavaScript is slow mmmkay http://t.co/NbB4eQjw - Actually, no, it's not http://t.co/kpGEIoPO #nodejs
about 20 hours ago from webTFS: It's super expensive, so it must be brilliant, right? Like Sharepoint #tekpubtfstitlesuggestion
5:22 PM February 3rd from web