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-03-01 : 08:48:14

Simon writes "Love the CASE WHEN approach, very nifty once I got it working!

Here's a thing - I would like to be able to sort by more than one field

--Existing Code--

....ORDER BY

CASE
WHEN @OrderBy = 1 THEN vw_Candidates.CandidateID
WHEN @OrderBy = 3 THEN vw_Candidates.DOB
WHEN @OrderBy = 6 THEN vw_Candidates.LastUpdated
END ASC,

CASE
WHEN @OrderBy = 2 THEN vw_Candidates.Surname
WHEN @OrderBy = 4 THEN vw_Candidates.myLocation
WHEN @OrderBy = 5 THEN vw_Candidates.myCat
WHEN @OrderBy = 7 THEN vw_Candidates.WhereIsCV
END ASC

--Existing Code--

What I'd like to be able to do is when @OrderBy is 2, to

ORDER BY vw_Candidates.Surname ASC, vw_Candidates.FirstName ASC

Any ideas?

Thanks!

simon

SQL Server 7.0
Win NT 4.0"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-01 : 11:08:02

....ORDER BY

CASE
WHEN @OrderBy = 1 THEN vw_Candidates.CandidateID
WHEN @OrderBy = 3 THEN vw_Candidates.DOB
WHEN @OrderBy = 6 THEN vw_Candidates.LastUpdated
END ASC,

CASE
WHEN @OrderBy = 2 THEN vw_Candidates.Surname
WHEN @OrderBy = 4 THEN vw_Candidates.myLocation
WHEN @OrderBy = 5 THEN vw_Candidates.myCat
WHEN @OrderBy = 7 THEN vw_Candidates.WhereIsCV
END ASC,

case
WHEN @OrderBy = 2 THEN vw_Candidates.FirstName
END ASC

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources