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

 

[essa2000]

Dear sarahw;
Here is solution of your problem. Check it , use pubs database.

*****************************************************
CREATE PROCEDURE sample
@Sort varchar(20)

AS

Declare @SQL nvarchar(500)

Set @SQL = 'SELECT * FROM AUTHORS ORDER BY ' + @Sort

Exec (@SQL)
*****************************************************

Regards,
essa2000