Solved

Using a variable in the ORDER BY clause of an SQL stored procedure.

Posted on 2010-08-26
4
337 Views
Last Modified: 2012-05-10
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?
0
Comment
Question by:thoecherl
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 33532078
as the error message indicates: change the field names into field positions (data type int), OR you have to change to a "dynamic" sql at some point
CREATE PROCEDURE udp_MULTI_SORT @FIELD1 VARCHAR(15), @FIELD2 VARCHAR(15)

AS

SELECT * 

 FROM RM00101 

ORDER BY CASE 

   WHEN @FIELD1 = 'Col1' THEN Col1 

   WHEN @FIELD1 = 'Col2' THEN Col2

   --- etc ----

  END

,CASE 

   WHEN @FIELD2 = 'Col1' THEN Col1 

   WHEN @FIELD2 = 'Col2' THEN Col2

   --- etc ----

  END



// note: in above suggestion, you might need to add more "case statements" to accomodate for different data types

 







OR, the quick and dirty method





CREATE PROCEDURE udp_MULTI_SORT @FIELD1 VARCHAR(15), @FIELD2 VARCHAR(15)

AS

exec(SELECT * FROM RM00101 ORDER BY [' + @FIELD1 + '],[' + @FIELD2 +']')

Open in new window

0
 
LVL 3

Expert Comment

by:firozmusthafa
ID: 33532094
try using "@field1 varchar(5) as feild1" and then in order by reference it as "fied1" your problem should be solved now
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 33532161
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.
0
 

Author Closing Comment

by:thoecherl
ID: 33532258
Thank you angellll.  The "quick and dirty" was quick, dirty, slick and accurate.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

4 Experts available now in Live!

Get 1:1 Help Now