At 09:12 AM 10/01/2007, Max Renshaw wrote:

>Is it not possible to:
>
>SELECT * FROM GET_DATA(IN_USER_ID) ORDER BY IN_ORDER_BY
>
>ie order outside the SP?

If IN_ORDER_BY is an output field of the selectable stored procedure
(which it's not in this example) you could. It would be a horrible
no-op, but you could....

You would need to define IN_ORDER_BY as a RETURNS() item and
implement some mechanism inside the processing loop in the SP to
increment this variable at each turn of the loop. The entire
procedure would have to execute before there was anything to sort by,
i.e. the whole output set, encompassing all of the values of IN_ORDER_BY.

In practical terms it would be useless/pointless. For this
manufactured ordering field to be meaningful, your SP would need to
be processing the underlying records in the desired order...so you
are back to Square One, actually, still needing an input variable to
be used to form the set either conditionally or using EXECUTE
STATEMENT in a FOR loop...

./heLen