kfenske
asked on
Stored proc Sort by Variable
I would like to have a stored procedure result set sorted by a field I pass to the procedure as a parameter. I would also like to have the sort direction (asc/desc) determined by a parameter. I believe that to make this work it must be in a precompiled procedure and must specify the field name and not the column number because I've read this in documentation. I've tried every way I can think of and am wondering if I have to specify special pre-compile or binding rules on the procedure?
Here is my test code:
use pubs
go
drop proc kentest
go
create proc kentest (@vsort varchar(8)='au_lname')
as
select * from authors
order by @vsort
go
exec kentest @vsort='au_lname'
The error message I get is:
Msg 1008, Level 15, State 1
The select list item identified by the order by number '1' contains a variable as part of the expression that identifies a column position. Variables are only allowed when ordering by an expression referencing a column name.
Here is my test code:
use pubs
go
drop proc kentest
go
create proc kentest (@vsort varchar(8)='au_lname')
as
select * from authors
order by @vsort
go
exec kentest @vsort='au_lname'
The error message I get is:
Msg 1008, Level 15, State 1
The select list item identified by the order by number '1' contains a variable as part of the expression that identifies a column position. Variables are only allowed when ordering by an expression referencing a column name.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks