Order By parameter in a stored procedure using multiple columns

Pete_Burke
Pete_Burke used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Director
Commented:
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.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial