order by clause using input parameter

[sarahw]

I have a stored procedure which performs a query based on a number of input parameters. I need the resultset to be ordered by a user defined input parameter. When I pass a parameter called sort (which is the column name) and refer to it in the order by clause, the syntax checker throws up an error message.

Does anyone know of a way to do this?

Many thanks

 

[MikeJones]

What Database are you running this on?

 

[sarahw]

Thanks for responding.
Its running on SqlServer 2000 - any ideas?

 

[MikeJones]

Sorry not a clue I'm an Oracle Geezer. If I had to have a wild guess I'd say that it might be something to do with the way that SQLServer parses it and that it doesn't like a variable reference in the order by part of the query, but this is a guess. Have you tried posting the question in the SQL Server Forum?

 

[sarahw]

Yes - but no response as yet.

Thanks anyway.

 

[MalcolmW]

This sort of thing (pardon the pun)?
CREATE PROCEDURE spTestSarahW
@Sortparam varchar(10)
AS
SELECT *, @Sortparam AS Sort
FROM Table
WHERE Field = 1000
ORDER BY Sort Malcolm
Remember, if it wasn't for electricity, we'd be surfing the net by candlelight.

 

[sarahw]

Malcolm
Thanks for responding. I've tried your suggestion but the procedure seems to interpret the passed value as a string.

The application (written in Cold Fusion) allows users to sort their data by clicking on a column heading. I therefore need to pass the actual column name rather than a specific value within that column.

Any ideas?

 

[MalcolmW]

I misinterpreted your requirement - this should do it.
CREATE PROCEDURE spTestSarahW
@SortFieldName varchar(50)
AS
EXEC ('SELECT * FROM Table ORDER BY ' + @SortFieldName)

Malcolm
Remember, if it wasn't for electricity, we'd be surfing the net by candlelight.

 

[hussux]

For 'Opus' Perhaps???