Order By parameter in a stored procedure using multiple columns

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
Pete_BurkeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John EastonDirectorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.