Solved

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

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

738 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