Solved

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

Posted on 2010-08-26
4
343 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 143

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 29

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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 article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

624 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