Is it not possible to:

SELECT * FROM GET_DATA(IN_USER_ID) ORDER BY IN_ORDER_BY

ie order outside the SP?

MAx

> At 06:30 AM 10/01/2007, you 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
>
> Not possible to do it this way. You can't pass parameters that alter
> the structure of a DSQL specification....so you can't have a
> replaceable output list or ordering or grouping criterion. You can
> parameterise WHERE criteria only.
>
> In Fb 1.5 and higher you can construct the entire SELECT statement as
> a string and pass that string to EXECUTE STATEMENT. You can
> alternatively do what we used to do before we had EXECUTE
> STATEMENT: branch your procedure according to the value of some
> input argument and write conditional SELECT statements. This
> obviously requires you to restrict the allowable values of this input
> variable to a condition that your procedure can handle.
>
>
>>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');
>
> Sure, using one of the approaches mentioned above.
>
>
>>I need to be able to ORDER BY column aliases(ITEM_NAME) and not the
>>actual columns(D.DIR_NAME).
>
> That's a different issue. When you alias a column, you are
> specifying a derived output field that is created from an
> expression. In v.1.5 you have to use the degree number (position
> number) in the ORDER BY clause, or otherwise restate the
> expression. (If you are using an alias merely to rename the column,
> then you should specify the ordering on the qualified actual column
> identifier or the degree number, since there is no expression to
> restate...)
>
> In v.2.0, you can use the alias identifier directly in the Order By
> clause.
>
> ./heLen
>