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)
Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.
