Hi All
can someone please help, i am writing my first update procedure and
am having a problem with the error trapping.
in my strored procedure (Update section), if i pass it a non existant
record key (IP_FKey) then the SQL does not jump to the WHEN ANY
statement but continues as if the update succeeded.

My question is: How do I test for an error such as failed update.
From reading the pdf's i was under the impression that this should
have worked?

My stored proc:
SET TERM ^^ ;
CREATE PROCEDURE UP_CATEGORY (
IP_METHOD SmallInt,
IP_FKEY Integer,
IP_CREATEDATE Date,
IP_CREATETIME Time,
IP_CODE VarChar(20),
IP_DESCE VarChar(80),
IP_EUSER VarChar(30))
returns (
RESULT VarChar(80))
AS
DECLARE VARIABLE W_TABLENAME VARCHAR(20);
DECLARE VARIABLE W_RESULT VARCHAR(3);
begin
W_TABLENAME = 'CATEGORY';
RESULT = 'NOK';
W_RESULT = '';
IF (IP_METHOD = 1) THEN
BEGIN
INSERT INTO CATEGORY(FKEY, CREATEDATE, CREATETIME, CODE, DESCE,
EUSER)
VALUES (GEN_ID(GEN_CATEGORY,
1), :IP_CREATEDATE, :IP_CREATETIME, :IP_CODE, :IP_DESCE, :IP_EUSER);
EXECUTE PROCEDURE
EP_REGISTER_EVENT :W_TABLENAME, :IP_FKEY, :IP_METHOD
RETURNING_VALUES :W_RESULT;
RESULT = W_RESULT;
WHEN ANY DO
BEGIN
RESULT = 'NOK - sqlcode = ' || SQLCODE;
EXIT;
END
END
ELSE
BEGIN
UPDATE CATEGORY
SET CREATEDATE = :IP_CREATEDATE,
CREATETIME = :IP_CREATETIME,
CODE = :IP_CODE,
DESCE = :IP_DESCE,
EUSER = :IP_EUSER
WHERE FKEY = :IP_FKEY;
EXECUTE PROCEDURE
EP_REGISTER_EVENT :W_TABLENAME, :IP_FKEY, :IP_METHOD
RETURNING_VALUES :W_RESULT;
RESULT = W_RESULT;
WHEN ANY DO
BEGIN
RESULT = 'NOK - SqlCode = ' || SQLCODE;
EXIT;
END
END
end
^^
SET TERM ; ^^

Any help will be appreciated

Thanks

Christopher