Optimizing dynamic sort - 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 2005 Forums

Transact-SQL (2005)

Optimizing dynamic sort

Author

Topic

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-02-06 : 13:15:13

My fiancee is currently developing a stored procedure for her project that uses a dynamic sort. Currently she's using a series of case statements to achieve the sort results. Her case statements are as follows:

case when (@SortDirection = 'asc' and @SortBy = 'Account') then [_AccountNumber] end asc,
case when (@SortDirection = 'asc' and @SortBy = 'Date') then NoteDate end asc,
case when (@SortDirection = 'asc' and @SortBy = 'TransDate') then NoteDate end asc,
case when (@SortDirection = 'asc' and @SortBy = 'User') then UserName end asc,
case when (@SortDirection = 'asc' and @SortBy = 'Type') then ShortDescription end asc,
case when (@SortDirection = 'asc' and @SortBy = 'Note') then Note end asc,
case when (@SortDirection = 'desc' and @SortBy = 'Account') then [_AccountNumber] end desc,
case when (@SortDirection = 'desc' and @SortBy = 'Date') then NoteDate end desc,
case when (@SortDirection = 'desc' and @SortBy = 'TransDate') then NoteDate end desc,
case when (@SortDirection = 'desc' and @SortBy = 'User') then UserName end desc,
case when (@SortDirection = 'desc' and @SortBy = 'Type') then ShortDescription end desc,
case when (@SortDirection = 'desc' and @SortBy = 'Note') then Note end desc
case when (@SortDirection NOT IN('asc','desc') OR @SortBy NOT IN('Account','Date','TransDate','User','Type','Note') then NoteDate end desc
case when (@SortDirectin IS NULL OR @SortBy IS NULL) then NoteDate end desc

Do you guys know of any way to combine these into a single case statement? It seems like it should be possible but I'm not sure how one would go about it. Thanks!

Some days you're the dog, and some days you're the fire hydrant.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 08:37:59

is this being used in ORDER BY? if yes, i dont think you can combine this into single CASE.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-02-10 : 11:27:28

Yes, this query is used for the ORDER BY. I guess I'll have her leave it the way it is then. Thanks!

Some days you're the dog, and some days you're the fire hydrant.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources