Link to home
Start Free TrialLog in
Avatar of kfenske
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.
ASKER CERTIFIED SOLUTION
Avatar of mikkon
mikkon

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kfenske
kfenske

ASKER

Excellent answer!

Thanks