Hi,

Yes you are right. thanks.

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 ; ^

On 21 October 2011 05:22, Thomas Steinmaurer <ts@...> wrote:

> **
>
>
> > 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
>
>
>


[Non-text portions of this message have been removed]