4

What is the best way to dynamically set the 'order by' column name and direction from parameters passed in to a plsql procedure?

haymansfield
  • 5,419
  • 4
  • 34
  • 51

2 Answers2

11

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
  • 232,371
  • 49
  • 380
  • 404
  • 1
    @Andomar, How to to specify DESC in your example? – Michael Pakhantsov Aug 26 '10 at 11:42
  • 1
    @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 Aug 26 '10 at 12:15
  • This is looking like the winner to me. Are there any potential problems with this approach? – haymansfield Aug 26 '10 at 14:45
  • Is it true that you can't use columns of different types in the case statement? – haymansfield Aug 26 '10 at 15:53
  • @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 Aug 26 '10 at 21:42
3

use dynamic sql

http://download.oracle.com/docs/cd/B10500_01/appdev.920/a96590/adg09dyn.htm

Michael Pakhantsov
  • 24,855
  • 6
  • 60
  • 59