How to ORDER BY using an @parameter

Question

How to ORDER BY using an @parameter

Jim W. White

Old Hand

Points: 346
More actions
November 24, 2004 at 12:17 pm

#60888

Is it possible to take in a parameter and use it as the column name or column number in the ORDER BY clause of a SQL statement? I've copied the SP_Columns stored procedure and created a new one from it. I want to specify a sort order as one of the parameters when I run the new SP_Columns_Sorted procedure.  It works fine if I hard code the value 4 in the ORDER BY clause but I'd like to take it one step further and be able to specify any column to sort on.   I modified the procedure to add a new parameter as so:
CREATE PROCEDURE sp_columns_sorted(
     @table_name  nvarchar(384),
     @table_owner  nvarchar(384) = null,
     @table_qualifier sysname = null,
     @column_name  nvarchar(384) = null,
     @ODBCVer   int = 2,
     @Order_By   nvarchar(384))
AS
....
Then I modified the two ORDER BY clauses to be:
ORDER BY @Order_By
and I get back the following message when I try to run the CREATE PROCEDURE code:
Server: Msg 1008, Level 15, State 1, Procedure sp_columns_by_name, Line 113
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
Any help would be greatly appreciated!
Jim

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

You may try CASE statement like

ORDER BY

  CASE WHEN @ColumnName='LastName' THEN LastName

       WHEN @ColumnName='Salary' THEN CONVERT(varchar(50), Salary)

       WHEN @ColumnName='SSN' THEN SSN

END

Jim W. White Old Hand Points: 346 More actions · Answer 2

Is there no other way? Doesn't MS-SQL have a way of using the value of a variable to substitute in places like the ORDER BY clause? I believe I've seen T-SQL code that uses a @varname parameter to substitute for a column name in the SELECT portion of a query.

Jim

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

Or try dynamic query.

declare @cmd varchar(1024)

declare @orderby varchar(10)

select @cmd = "select * from authors order by " + @orderby

exec (@cmd)

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

May I add that I would prefer sp_executeSQL rather than EXEC() if it need to be dynamic?

Why? http://www.sommarskog.se/dynamic_sql.html

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]