Hi Vlad!

I think the solution is very simple. What you've written is a selectable
stored procedure, and that is not supposed to be called using EXECUTE
PROCEDURE, but simply SELECT.

So, what I'd do is to remove the IN_ORDER_BY from the procedure
altogether and simply call it by writing:

SELECT OUT_NAME, OUT_SIZE
FROM GET_DATA(1)
ORDER BY OUT_NAME

or

SELECT OUT_NAME, OUT_SIZE
FROM GET_DATA(1)
ORDER BY OUT_NAME, OUT_SIZE

Though you'll only be able to order by fields that are returned from the
stored procedure.

HTH,
Set


Vlad Orlovsky wrote:

> Hi All,
>
> I would like to be able to pass in an IN_ORDER_BY parameter into the SP and have it sort the resultset based on that parameter.
>
> My SP(simplified version):
>
> CREATE PROCEDURE GET_DATA(
> IN_USER_ID integer,
> IN_ORDER_BY varchar(32)
> )
> RETURNS (
> OUT_NAME varchar(256) character set unicode_fss,
> OUT_SIZE integer
> )
> AS
> BEGIN
> FOR
> SELECT
> D.DIR_NAME ITEM_NAME,
> 0 ITEM_SIZE
> FROM DIRS D
> WHERE D.USER_ID = :IN_USER_ID
> ORDER BY :IN_ORDER_BY
> INTO
> OUT_NAME,
> OUT_SIZE
> DO
> BEGIN
> SUSPEND;
> END
> END
>
> Is it possible to get something like this to work:
> execute procedure GET_DATA(1, 'ITEM_NAME DESC');
> execute procedure GET_DATA(1, 'ITEM_NAME DESC, ITEM_SIZE ASC');
>
> I need to be able to ORDER BY column aliases(ITEM_NAME) and not the actual columns(D.DIR_NAME).
>
> Thank you in advance,
> Vlad