Dynamic Order Type - 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

Transact-SQL (2000)

Dynamic Order Type

Author

Topic

jeffj
Starting Member

5 Posts

Posted - 2004-07-29 : 13:16:59

I am having trouble figuring out how to create a truly dynamic Order By clause.

I know I can use the case statements to build my dynamic columns but how do you specify ASC or DESC dynamically?

select contactID
from contactacts
ORDER BY case
when ( @orderBy = 'email' ) then email
when ( @orderBy = 'lastName' ) then lastName
else NULL
end
asc

How can I make the "asc" in the above dynamic so I can specify ASC or DESC?

Thanks in advance for your help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-29 : 13:23:14

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=5942

Tara

jeffj
Starting Member

5 Posts

Posted - 2004-07-29 : 13:54:57

Ah of course... Celko's answer makes perfect sense.

He did of course leave off one important tid bit though, the desc keyword on sort_2 in the order by clause.

SELECT ..
CASE WHEN @sort_col = 'A'
AND @sort_order = 'ASC'
THEN A
WHEN @sort_col = 'B'
AND @sort_order = 'ASC'
THEN B
ELSE NULL END AS sort_1,
CASE WHEN @sort_col = 'A'
AND @sort_order = 'DESC'
THEN A
WHEN @sort_col = 'B'
AND @sort_order = 'DESC'
THEN B
ELSE NULL END AS sort_2
FROM Foobar
WHERE ..
ORDER BY sort_1 asc, sort_2 desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources