Solved

Order By parameter in a stored procedure using multiple columns

Posted on 2014-02-26
2
1,872 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
[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
2 Comments
 
LVL 11

Accepted Solution

by:
John Easton 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 66

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

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.
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

623 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