Solved

Order By parameter in a stored procedure using multiple columns

Posted on 2014-02-26
2
1,773 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 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 to SQL Server 2016 24 53
SQL Percentage Formula 7 30
Begin Transaction 12 25
Report 8 24
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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…
Viewers will learn how the fundamental information of how to create a table.

737 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