Using a variable in the ORDER BY clause of an SQL stored procedure.
I am writing a stored procedure which will be used to run a SELECT query with multiple ORDER BY columns. I will pass in the ORDER BY parameters -- @FIELD1, @FIELD2, for example. Problem is, SQL won't let me use a variable in the ORDER BY clause.
I want the proc to look like this:
CREATE PROCEDURE udp_MULTI_SORT @FIELD1 VARCHAR(15), @FIELD2 VARCHAR(15)
AS
SELECT * FROM RM00101 ORDER BY @FIELD1, @FIELD2
The SQL error I get when I try this is:
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.
Does anyone know a way around this?
I want the proc to look like this:
CREATE PROCEDURE udp_MULTI_SORT @FIELD1 VARCHAR(15), @FIELD2 VARCHAR(15)
AS
SELECT * FROM RM00101 ORDER BY @FIELD1, @FIELD2
The SQL error I get when I try this is:
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.
Does anyone know a way around this?
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
firozmusthafa
try using "@field1 varchar(5) as feild1" and then in order by reference it as "fied1" your problem should be solved now
Hi,
Try using a CASE statement, something like:
CREATE PROCEDURE udp_MULTI_SORT @FIELD1 VARCHAR(15), @FIELD2 VARCHAR(15)
AS
set @FIELD1 ='Some fieldname'
@FIELD2 = 'some other fieldname'
--Then
SELECT * FROM RM00101
ORDER BY CASE @FIELD1
WHEN 'somefieldname' THEN Somefieldname
CASE @FIELD2
WHEN...
END
Else
select * from FROM RM00101
something like that.
That may need some tweaking but the context is sound.
Try using a CASE statement, something like:
CREATE PROCEDURE udp_MULTI_SORT @FIELD1 VARCHAR(15), @FIELD2 VARCHAR(15)
AS
set @FIELD1 ='Some fieldname'
@FIELD2 = 'some other fieldname'
--Then
SELECT * FROM RM00101
ORDER BY CASE @FIELD1
WHEN 'somefieldname' THEN Somefieldname
CASE @FIELD2
WHEN...
END
Else
select * from FROM RM00101
something like that.
That may need some tweaking but the context is sound.
ASKER
Thank you angellll. The "quick and dirty" was quick, dirty, slick and accurate.