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)