Hi,

With have an intermittent problem with a stored procedure. We're using
Firebird 1.03 on Windows NT. This procedure gets called about 5000 times
a day and works fine except for roughly 1 or 2 calls per day where we
get the error:

Dynamic SQL Error
SQL error code = -84
procedure GET_SEQUENCENEXTKEYS does not return any values
At line 1, column 15

This make no sense to me. The code for the stored procedure is

COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

/* Stored procedures */

CREATE PROCEDURE "GET_SEQUENCENEXTKEYS"
(
"KEYPREFIX" CHAR(30),
"NEXTKEYINCREMENT" INTEGER
)
RETURNS
(
"THENEXTKEY" INTEGER
)
AS
BEGIN EXIT; END ^


ALTER PROCEDURE "GET_SEQUENCENEXTKEYS"
(
"KEYPREFIX" CHAR(30),
"NEXTKEYINCREMENT" INTEGER
)
RETURNS
(
"THENEXTKEY" INTEGER
)
AS
DECLARE VARIABLE TheLockFlag INT;
DECLARE VARIABLE IncrementedNextKey INT;
BEGIN
BEGIN
SELECT NextKey, LockFlag FROM System_SequenceNextKey WHERE Prefix =
:keyPrefix
INTO :TheNextKey, :TheLockFlag;
WHEN GDSCODE deadlock, GDSCODE lock_conflict DO
TheLockFlag = 1;
END
IF (TheLockFlag = 1) THEN BEGIN
TheNextKey = -1;
SUSPEND;
EXIT;
END
BEGIN
IncrementedNextKey = TheNextKey + NEXTKEYINCREMENT;
UPDATE System_SequenceNextKey SET NextKey=:IncrementedNextKey WHERE
Prefix = :keyPrefix;
WHEN GDSCODE deadlock, GDSCODE lock_conflict DO BEGIN
TheNextKey = -1;
SUSPEND;
EXIT;
END
END
BEGIN
/* Worth checking but could fail if someone gets in before us. */
SELECT NextKey FROM System_SequenceNextKey WHERE Prefix = :keyPrefix
INTO :IncrementedNextKey;
WHEN GDSCODE deadlock, GDSCODE lock_conflict DO BEGIN
TheNextKey = -1;
SUSPEND;
EXIT;
END
END
IF (IncrementedNextKey <> (TheNextKey + NEXTKEYINCREMENT)) THEN BEGIN
TheNextKey = -1;
SUSPEND;
EXIT;
END
SUSPEND;
END
^

SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;