Home » Using ORDER BY Based on a Parameter

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 customersORDER 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))asdeclare @string varchar(1000)select @string = 'select * from authors order by ' + @orderbyexec (@string)

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.