"Dynamic" sorting inside a procedure

[Michal Grabowski]

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

[Dan Guzman]

One 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" <mg******@elka.pw.edu.pl> wrote in message
news:cf**********@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

[Erland Sommarskog]

Michal Grabowski (mg******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Michal Grabowski]

Thanks for all tips!

Regards,
Mike