What is it you are trying to accomplish with the stored procedure? If
you are only interested ordering the reservation by start time, then
you could use a much simpler approach.

select *
from reservation
order by timestart

I also wasn't aware that you could use a stored procedure inside an
order by clause, and am struggling to find an occasion that you would
actually want to. Order by accepts a list of field names rather than
values.

Adam


--- In firebird-support@yahoogroups.com, "Henk van der Meer"
<hvdmeer@c...> wrote:

> Hello,
>
> I'm trying to use a stored procedure in a order by clause.
> I thought I was doing OK, I am not very fluent in SP's, until I

needed a

> second parameter.
>
> When I do something like this (I created some simple example to

reproduce):

>
> order by (select OUT_TIMESTART from
> RESERVATION_START(reservation.RESERVATIONID))
>
> it works OK.
>
> When I change the stored procedure so that it needs 2 parameter (I

don't

> even use the second parameter yet) I get an error
>
> order by (select OUT_TIMESTART from
> RESERVATION_START(reservation.RESERVATIONID,1))
>
> --------
> ISC ERROR CODE:335544569
>
> ISC ERROR MESSAGE:
> Dynamic SQL Error
> SQL error code = -104
> Token unknown - line 2, char 81
> ASC
>
> Statement: select * from reservation
> order by (select OUT_TIMESTART from
> RESERVATION_START(reservation.RESERVATIONID,1))
> --------
>
> I tried the using the same SP in the select or where clause. There

it works

> OK.
>
> To reproduce the problem I've added some sql.
>
> Could somebody please tell me what is going on?
>
> Thank you,
>
> Henk van der Meer
>
> ------ sql code to reproduce the problem -----
>
> /* Remove comment for second try */
>
> /*
> DROP PROCEDURE RESERVATION_START;
> drop TABLE RESERVATION;
> */
>
> CREATE TABLE RESERVATION
> (
> RESERVATIONID INTEGER,
> PERSONS INTEGER,
> TIMESTART TIMESTAMP
> );
>
> insert into reservation values(1,10,'1-8-2004');
> insert into reservation values(2,20,'1-7-2004');
> insert into reservation values(3,30,'1-6-2004');
> insert into reservation values(4,40,'1-5-2004');
> insert into reservation values(5,50,'1-4-2004');
> insert into reservation values(6,60,'1-3-2004');
> insert into reservation values(7,70,'1-2-2004');
> insert into reservation values(8,80,'1-1-2004');
>
>
> COMMIT WORK;
> SET AUTODDL OFF;
> SET TERM ^ ;
>
> /* Stored procedures */
>
> CREATE PROCEDURE "RESERVATION_START"
> (
> "IN_RESERVATIONID" INTEGER
> )
> RETURNS
> (
> "OUT_TIMESTART" TIMESTAMP
> )
> AS
> BEGIN EXIT; END ^
>
>
> ALTER PROCEDURE "RESERVATION_START"
> (
> "IN_RESERVATIONID" INTEGER,
> "SECONDPARAM" INTEGER
> )
> RETURNS
> (
> "OUT_TIMESTART" TIMESTAMP
> )
> AS
>
> BEGIN
> select TIMESTART
> from reservation
> WHERE reservation.RESERVATIONID = :IN_RESERVATIONID
> INTO :OUT_TIMESTART;
> SUSPEND;
> END
> ^
>
> SET TERM ; ^
> COMMIT WORK;
> SET AUTODDL ON;
>
> select * from reservation
> order by (select OUT_TIMESTART from
> RESERVATION_START(reservation.RESERVATIONID,1))