Using multiple sp parameters for ORDER BY

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using multiple sp parameters for ORDER BY

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-07-13 : 07:44:52

Pablo writes "SQL 2000 STD SP4, Windows 2000 Server SP4

I want to to use parameters to specify the ORDER BY clause using multiple field names. I would like to pass a parameter or multiple parameters to one stored procedure to change the way the data is sorted in a select statement base upon multiple columns in every possible combination of 3 columns.

Selectable Sorting Options:

Project, ActivityDate, Activity
Project, Activity, ActivityDate
ActivityDate, Activity, Project
ActivityDate, Project, Activity
Activity, ActivityDate, Project
Activity, Project Activity, Date

These are just 3 of 6 fields I would like to be able to customize the ORDER BY combinations.

I've tried using CASE ...

ORDER BY
CASE @Sort1
WHEN 'Project' THEN Project
WHEN 'ActivityDate' THEN ActivityDate
WHEN 'Activity' THEN Activity
END
,
CASE @Sort2
WHEN 'Project' THEN Project
WHEN 'ActivityDate' THEN ActivityDate
WHEN 'Activity' THEN Activity
END
,
CASE @Sort3
WHEN 'Project' THEN Project
WHEN 'ActivityDate' THEN ActivityDate
WHEN 'Activity' THEN Activity
END

But SQL doesn't allow it becuase items the ORDER BY clause are not unique.

I tried using Dynamic sql with the EXEC (sqlstring) + 'ORDER BY ' + @SortOrder without any success. Not sure of syntax.

Is there anyway to pass the entire ORDER BY string as a parameter consisting of multiple field names?

Or

Is there a way to use an IF THEN statement dependent on a parameter to change the ORDER BY using mulitple field names?

Or

Do I have to create a stored procedure for each ORDER BY combination and then change the sp to use base upon the sort selection?

I have searched hi and low and have found many solutions for sorting by 1 column using a parameter but not multiple columns in various orders.

Your prompt assistance is appreciated."

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-07-13 : 08:39:30

Perhaps you could sort it in your presentation layer? Otherwise, i would take a shot at Dynamic SQL.

You would build your string then exe it, like so : SET @SQL = 'Select <list> from <table> where <condition> ORDER BY ' + @passedinsortorder

Then you can EXEC(@SQL)

Probably not the best idea, but it would work.

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources