Hi,

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?

Regards

Ben


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