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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-05-06 : 07:58:02
|
| Eyal writes "Hi,I am looking for a way to create a dynamic order by and sort direction sections (ASC/DESC) in my stored procedures. I do not wish to use the exec statement due to the fact that it will recompile the stored procedure every execution. I know I can do it with a case statement in the order by section however, I cannot do the same for the sort direction. If you can somehow have an idea or a way to achieve this I would be very grateful.Thank you very much for your time.Eyal" |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-05-06 : 08:09:49
|
| There are a few articles on SQLTEAM about dynamic order and dynamic where.To find the articles, enter dynamic order in the Article Search input box. One of the first hits that will show up is:Dynamic ORDER BY on 1/22/2001[url]http://sqlteam.com/item.asp?ItemID=2209[/url]The other articles on dynamic SQL are equally interesting.HTH,Sam |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-05-06 : 08:17:37
|
| Also -- read the comments after the articles, that is where what you ask for is discussed.if the field is numeric, it is easy:ORDER BY NumberField * CASE WHEN order='asc' then 1 else -1 endif the field is not numeric, then I usually do something like this:ORDER BY CASE WHEN order='asc' then Field else '' END ASC, CASE WHEN order='desc' then Field else '' END DESC- Jeff |
|
|
|
|
|
|
Dynamic order by and sort direction