Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.

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



Application Security Testing: An Integral Part of DevOps

Using ORDER BY Based on a Parameter

I want to construct an SQL query or stored procedure that will order its result set based on a query parameter. Something like:
FROM customers
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!

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)
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.



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