Dynamic Sort Order with Descend Option

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 Sort Order with Descend Option

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-30 : 08:25:27

Ken writes "Thank you for the info on using the dynamic ORDER BY clause. (ItemID=2209). I'd like to take this one step further. Do you know of a way to dynamically specify the order direction (ASC or DESC) without duplicating the entire SELECT statement for each case? (I've tried a Case statement, but SQL Server doesn't like that.)

Using SQL Server 2000 on Windows 2000 Server.

Thanks in advance.

Ken Becker"

Nazim
A custom title

1408 Posts

Posted - 2002-01-30 : 08:46:54

something on this line should help you out

create procedure descOrder(@srtOrd Varchar(4))
as
declare @strSelect varchar(30)

select @strSelect="select * from tablename order by colunmname "+ @strOrd --@strOrd can contain either Asc or Desc

exec(@strSelect)

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-01-30 : 16:07:44

You can avoid dynamic SQL and do it with multiple case statements. The following code takes the parameter @iOrd. When @iOrd is 1 or 2, it orders by a or b (both ascending). When @iOrd is 3, the first case always evaluates to 1, so the second case matters.

Cheers
-b


select a,b,c
  from table
order by
  case @iOrd
     when 1 then a
     when 2 then b
     else 1
  end ASC,
  case @iOrd
     when 3 then c
     else 1
  end DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources