How to ORDER BY using an @parameter
-
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
-
You may try CASE statement like
ORDER BYCASE WHEN @ColumnName='LastName' THEN LastNameWHEN @ColumnName='Salary' THEN CONVERT(varchar(50), Salary)WHEN @ColumnName='SSN' THEN SSNEND
-
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
-
Or try dynamic query.
declare @cmd varchar(1024)
declare @orderby varchar(10)
select @cmd = "select * from authors order by " + @orderby
exec (@cmd)
-
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]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply