Hi,

I'm a new in using stored procedures in firebird. I'm trying to catch
'Exceptions' in a stored procedure in such way it will skip rows for
which
the exception occured but will continue to loop the remaining rows.
The body
of the procedure consists of 2 updates on 2 different tables. I have
3 rows
in table 1 and 3 rows in table 2. When fi row 2 from table 2 is in a
state
that it has been updated but not committed a deadlock will occur when
the
procedure is executed by some other user. After executing the
procedure I
get 3 updated rows in table 1 and 2 updated rows in table 2, so I have
inconsistent data in my db. I thought that everything within the
begin..end
block would be 'undone' when an exception occured. What i want to
achieve is
to loop each row in table 1, do the updates, if all updates succeeded,
continue looping. When some exception occured within the for select
loop,
ALL changes have to be 'undone' and continue looping.

Greetings,
Filip Moons

CREATE PROCEDURE SET_STATUS (
RETURNERRORS SMALLINT)
RETURNS (
ERRORCODE VARCHAR(20),
ERRORVALUE INTEGER,
ERRORTEXT VARCHAR(80))
AS
DECLARE VARIABLE AID INTEGER;
DECLARE VARIABLE ACODE VARCHAR(8);
DECLARE VARIABLE ABEDRAG NUMERIC(15,2);
DECLARE VARIABLE ASTATUS SMALLINT;
begin
FOR
SELECT TSTID,TSTCODE,TSTBEDRAG,TSTSTATUS
FROM TEST
ORDER BY TSTID
INTO AID,ACODE,ABEDRAG,ASTATUS
DO
BEGIN
UPDATE TEST
SET
TSTSTATUS = 1
WHERE
TSTID = :AID;

UPDATE TEST_CODE
SET
TCBEDRAG = TCBEDRAG + :ABEDRAG
WHERE
TCCODE = :ACODE;

WHEN ANY DO
BEGIN
if (RETURNERRORS <> 0) then
BEGIN
ERRORCODE = 'SKIPPED';
ErrorValue = GDSCODE;
ErrorText = CAST(AID as VARCHAR(80));
SUSPEND;
END
END
END
end