Using a variable in the ORDER BY clause of an SQL stored procedure.
Posted on 2010-08-26
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)
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?