"Dynamic" sorting inside a procedure

Hello,
I need to have a stored procedure, which performs sorting. Something like
this:

CREATE PROCEDURE procname
@.sortby varchar(30)
AS
BEGIN
SELECT some, columns
FROM some_table
ORDER BY @.sortby
END

(of course, i know this won't work, but it gives the idea of what i mean)
Is there a possibility to write a procedure which behaves like that? It is
important for me not to have multiple procedures just for different sorting
criteria...

Thanks,
MikeOne method is to build and execute a dynamic SQL statement. For example:

EXEC
(
'SELECT some, columns
FROM some_table
ORDER BY ' + @.sortby
)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Michal Grabowski" <mgrabow1@.elka.pw.edu.pl> wrote in message
news:cf5h33$g1j$1@.julia.coi.pw.edu.pl...
> Hello,
> I need to have a stored procedure, which performs sorting. Something like
> this:
> CREATE PROCEDURE procname
> @.sortby varchar(30)
> AS
> BEGIN
> SELECT some, columns
> FROM some_table
> ORDER BY @.sortby
> END
> (of course, i know this won't work, but it gives the idea of what i mean)
> Is there a possibility to write a procedure which behaves like that? It is
> important for me not to have multiple procedures just for different
sorting
> criteria...
> Thanks,
> Mike|||Michal Grabowski (mgrabow1@.elka.pw.edu.pl) writes:
> CREATE PROCEDURE procname
> @.sortby varchar(30)
> AS
> BEGIN
> SELECT some, columns
> FROM some_table
> ORDER BY @.sortby
> END
> (of course, i know this won't work, but it gives the idea of what i
> mean) Is there a possibility to write a procedure which behaves like
> that? It is important for me not to have multiple procedures just for
> different sorting criteria...

http://www.sommarskog.se/dynamic_sql.html gives some suggestions.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for all tips!

Regards,
Mike