> 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
