Solved

Order By parameter in a stored procedure using multiple columns

Posted on 2014-02-26
2
1,706 Views
Last Modified: 2014-03-02
Hi All

I'm writing a stored procedure that uses a parameter for the order by clause in a select statement.

The order by has to use single or multiple columns. For example using the adventureWorks2008 database and a query like.

SELECT  FirstName , MiddleName , LastName  
FROM [AdventureWorks2008].[Person].[Person]
ORDER BY FirstName , MiddleName , LastName ASC

But in the stored procedure I want to replace the order by clause with a passed in parameter @OrderBy

@OrderBy can be any combination of the columns.
Such as @OrderBy = 'FirstName, MiddleName, LastName' ASC
or @OrderBy = 'LastName, FirstName' DESC
or @OrderBy = 'LastName' ASC
etc..

I can get it working with a single column in the passed in order by clause but not with multiple columns. How do I do that?

Regards,

Pete
0
Comment
Question by:Pete_Burke
2 Comments
 
LVL 10

Accepted Solution

by:
JEaston earned 500 total points
ID: 39888326
Hi Pete,

I'm not sure if this is the most efficient option or not, but it should work.

DECLARE @query nvarchar(2000)
SET @query = 'SELECT  FirstName , MiddleName , LastName FROM [AdventureWorks2008].[Person].[Person] ORDER BY '+@OrderBy
print @query 
exec(@query)

Open in new window


Essentially you create the whole SQL statement and save it to a variable.  Then tell SQL to execute the commands in the variable.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39888646
Here's an article on SQL Server CASE Solutions, scroll down 2/3rds of the way and you'll see an example on ORDER BY with one column.

To do multiple columns, you could pass an int value (say 1=FirstName, MiddleName, LastName' , 2='LastName, FirstName' DESC) and it'll go like this..
ALTER PROC get_names (@sort_order int) AS 
SELECT name, group_id, is_active
FROM some_table
ORDER BY 
   -- First sort position
   CASE @sort_id 
      WHEN 1 THEN first_name
      WHEN 2 THEN last_name END,
	-- Second sort position
   CASE 
      WHEN 1 THEN middle_name
      WHEN 2 THEN first_name DESC END
   CASE 
      WHEN 1 THEN last_name END
GO

Open in new window

The only way I know to pull off passing a string value as a parameter (i.e. @OrderBy = 'FirstName, MiddleName, LastName ASC' would be to have the final set dynamic SQL
CREATE SP get_names(@sort_order varchar(100)) AS

-- your SP here

-- return set 
Declare @sql varchar(1000) 
SET @sql = 'your SELECT clause without the ORDER BY here ' + @sort_order
exec @sql
GO

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying 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

Suggested Solutions

Title # Comments Views Activity
SQL replication over high latency link 10 58
VB.net Duplicating a table - primary key not created 3 31
SQL R 21 25
SQL View / Qtry 3 10
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

856 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