What is the best way to dynamically set the 'order by' column name and direction from parameters passed in to a plsql procedure?
+7
A:
You can use variables if you order using a case:
select *
from YourTable
order by
case when par_name = '1' then col1
when par_name = '2' then col2
end
, case when par_name = '3' then col3
end desc
Andomar
2010-08-26 11:38:25
@Andomar, How to to specify DESC in your example?
Michael Pakhantsov
2010-08-26 11:42:09
@Michael Pakhantsov: You can with a second `case`, added to the answer. If the parameter is 3, the first `case` is `null` for every row, so it sorts on the second case
Andomar
2010-08-26 12:15:17
@Andomar, thanks.
Michael Pakhantsov
2010-08-26 12:16:03
This is looking like the winner to me. Are there any potential problems with this approach?
haymansfield
2010-08-26 14:45:02
Is it true that you can't use columns of different types in the case statement?
haymansfield
2010-08-26 15:53:42
@haymansfield: Yeah, they have to result in the same type. You can cast them to the same type, or include an extra case like for the descending sort. When you get very complex, dynamic SQL is the answer
Andomar
2010-08-26 21:42:41