Solved

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

Posted on 2010-08-26
4
339 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

810 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