Solved

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

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

757 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

19 Experts available now in Live!

Get 1:1 Help Now