> I'm trying to create a stored procedure that executes another stored
> procedure for each record returned by the select.
>
> My firebird version is 2.1.3
>
> Here are my 2 stored procedures....
>
> SET TERM ^;
> CREATE OR ALTER PROCEDURE is_message_hidden( messageId NUMERIC(18,0),
> activeAtTime NUMERIC(18,0) )
> RETURNS (hidden char(1))
> AS
> BEGIN
> hidden = 1;
> END^
> SET TERM ; ^
>
> SET TERM ^;
> CREATE OR ALTER PROCEDURE get_active_messages_server( serverClientId
> INTEGER, activeAtTime NUMERIC(18,0) )
> RETURNS (
> id NUMERIC(18, 0),
> messageId NUMERIC(18,0),
> messageVersion NUMERIC(18,0),
> siteNumber NUMERIC(18,0),
> receivedTime NUMERIC(18,0),
> sentTime NUMERIC(18,0),
> expiryTime NUMERIC(18,0),
> slugline BLOB SUB_TYPE TEXT,
> hidden CHAR(1)
> )
> AS
> BEGIN
> FOR
> SELECT localid, messageid, messageVersion, siteNumber,
> localReceivedTime, sentTime, expireyTime, slugline
> FROM TYRDISP__MESSAGE msg
> WHERE msg.localReceivedTime<= :activeAtTime AND msg.expireyTime>
> :activeAtTime
> INTO :id, :messageId, :messageVersion, :siteNumber, :receivedTime,
> :sentTime, :expiryTime, :slugline
> DO
> BEGIN
> EXECUTE PROCEDURE is_message_hidden(:messageId, activeAtTime)
> RETURNING_VALUES :hidden
> SUSPEND;
> END
> END^
> SET TERM ; ^
>
> When I use isql to execute the DDL the get_active_messages_server procedure
> fails with the following error
>
> SQL> SET TERM ^;
> SQL> CREATE OR ALTER PROCEDURE get_active_messages_server( serverClientId
> INTEGER, activeAtTime NUMERIC(18,0) )
> CON> RETURNS (
> CON> id NUMERIC(18, 0),
> CON> messageId NUMERIC(18,0),
> CON> messageVersion NUMERIC(18,0),
> CON> siteNumber NUMERIC(18,0),
> CON> receivedTime NUMERIC(18,0),
> CON> sentTime NUMERIC(18,0),
> CON> expiryTime NUMERIC(18,0),
> CON> slugline BLOB SUB_TYPE TEXT,
> CON> hidden CHAR(1)
> CON> )
> CON> AS
> CON> BEGIN
> CON> FOR
> CON> SELECT localid, messageid, messageVersion, siteNumber,
> localReceivedTime, sentTime, expireyTime, slugline
> CON> FROM TYRDISP__MESSAGE msg
> CON> WHERE msg.localReceivedTime<= :activeAtTime AND
> msg.expireyTime> :activeAtTime
> CON> INTO :id, :messageId, :messageVersion, :siteNumber, :receivedTime,
> :sentTime, :expiryTime, :slugline
> CON> DO
> CON> BEGIN
> CON> EXECUTE PROCEDURE is_message_hidden(:messageId,
> activeAtTime)
> CON> RETURNING_VALUES :hidden
> CON> SUSPEND;
> CON> END
> CON> END^
> Statement failed, SQLCODE = -104
> Dynamic SQL Error
> -SQL error code = -104
> -Token unknown - line 24, column 25
> -SUSPEND
> SQL> SET TERM ; ^
>
>
> Any ideas what I am doing wrong?

With a quick look, you are missing a semicolon at the end of the EXECUTE
PROCEDURE ... line.



--
With regards,
Thomas Steinmaurer

* Firebird Foundation Committee Member
http://www.firebirdsql.org/en/firebird-foundation/

* Upscene Productions - Database Tools for Developers
http://www.upscene.com/

* My Blog
http://blog.upscene.com/thomas/index.php