Dynamic Order BY - SQL Server Forums

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)

Dynamic Order BY

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-27 : 11:59:51

Matt writes "I was able to create a stored procedure using your Dynamic ORDER BY example (http://www.sqlteam.com/item.asp?ItemID=2209).

However, I get an error if I try to add more than one field to the sort statement, or sort an individual field in a different order.

For example, modifying the ORDER BY statement in the http://www.sqlteam.com/item.asp?ItemID=2209 example to the following would produce an error:


CREATE PROCEDURE ps_Player_SELECT_ByStats (
...
SELECT Pla_FName+' '+Pla_LName AS Name,
...
ORDER BY CASE WHEN @OrderBY = 1 THEN Pla_BattingAverage ASC
              WHEN @OrderBY = 2 THEN Pla_HomeRuns
              WHEN @OrderBY = 3 THEN Pla_RBIs, Pla_HomeRuns
              WHEN @OrderBY = 4 THEN Pla_StolenBases
              END DESC
...

Is there a way to sort by multiple fields or change the order? Thanks!"

jbkayne
Posting Yak Master

100 Posts

Posted - 2002-02-27 : 12:14:55

Try the following:

ORDER BY
CASE @OrderBY
WHEN 1 THEN Pla_BattingAverage
WHEN 3 THEN Pla_RBIs /*from your example I wasn't sure if you intended this one to be asc or desc*/
END
asc
,
CASE @OrderBY
WHEN 2 THEN Pla_HomeRuns
WHEN 3 THEN Pla_HomeRuns
WHEN 4 THEN Pla_StolenBases
END
desc

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-02-27 : 19:07:31

Try getting rid of the ending "DESC".
ORDER BY (case expression) DESC will produce
"ORDER BY Pla_BattingAverage ASC DESC" when @orderBy=1

Another way to do dynamic order by is store your result in a
temp table "#temp" and pass in the orderby clause.
--> exec('select * from #temp order by '+@orderby)

Edited by - lou on 02/27/2002 19:08:51

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources