I want to construct an SQL query or stored procedure that will order its result set based on a query parameter. Something like:
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!
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
declare @string varchar(1000)
select @string = 'select * from authors order by ' + @orderby