SQL Server Dynamic Order By Without EXEC/Strings

Here's a quick little tidbit that might be of use to you. If you need to dynamically adjust the ORDER BY clause of a query but do not want to use the EXEC SQLString methodology, try the following.

Here's a quick little tidbit that might be of use to you. If you need to dynamically adjust the ORDER BY clause of a query but do not want to use the EXEC SQLString
methodology, try the following. Remember though, the data type of the column must be the same in each THEN case. In other words, you couldn't sort on a datetime column or a varchar column based on CASE.

declare @OrderBy varchar(20)

select @Orderby = 'ContactName'

select * from Northwind.dbo.Customers

ORDER BY
CASE @OrderBy
WHEN 'CompanyName' THEN CompanyName
WHEN 'ContactName' THEN ContactName
END

ASC


Submission Date:  9/23/2005 2:55:51 PM
Submitted By:  Robbe Morris
My Home Page:  http://www.robbemorris.com

By Robbe Morris   Popularity  (281 Views)
Picture
Biography - Robbe Morris
Robbe has been a Microsoft MVP in C# since 2004. He is also the co-founder of NullSkull.com which provides .NET articles, book reviews, software reviews, and software download and purchase advice.  Robbe also loves to scuba dive and go deep sea fishing in the Florida Keys or off the coast of Daytona Beach. Microsoft MVP