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