Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Oct 6, 2000

Using ORDER BY Based on a Parameter

Question:
I want to construct an SQL query or stored procedure that will order its result set based on a query parameter. Something like:
SELECT *
FROM customers
ORDER BY ?
Unfortunately, that is not valid SQL. Is there a way to achieve this within SQL without having to resort to building the query in the calling application, or implementing the ordering in the calling application?

My environment is JDBC/SQL Server 7.0

Thanks very much! The data on your site is great!

Answer:
You have two options.

The first is to have a set of IF statements that executes the proper select statement for the requested order. This is certainly very doable, if inelegant, for a limited number of options.

The second way would be to build a dynamic SQL string using the parameter passed for the order.

Here is a proc to illustrate the technique:

create procedure dynamic_order
(
	@orderby varchar(100)
)
as
declare @string varchar(1000)

select @string = 'select * from authors order by ' + @orderby

exec (@string)
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date