[firebird-support] Stored Procedure Help

[Ben Short]

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]