How to get actual return code when SQL Continue Handler seems to zero it?

I created a Linux DB2 stored procedure for the purpose of either dropping or adding 7 constraints over 5 tables. I always want to do all 7 and just report individual return codes (SQLCODE) from each. But it seems the Continue Handler I am using zeros the return codes every time because if I run the DROP twice in a row they all report return code zero the second time--and that is just not correct.

Case p_Action
When 'DROP' Then
  Begin
    Declare Continue Handler for Not_Found Begin End;

    Set v_MsgText = 'DROP LOC Check Constraint failed.';
    Set v_SqlStmt = 'ALTER TABLE LIBRAT.APNVEND_RECORD_APNV_LOC_DATA DROP CONSTRAINT LIBRAT_APNVEND_RECORD_APNV_LOC_DATA_C1';
    Execute Immediate v_SqlStmt;
    Set v_MsgText = 'DROP LOC Check Constraint rc=' || SQLCODE;
    Call UTL_FILE.PUT_LINE(v_LogHandle, v_MsgText);
...snip...

Is there a technique for accomplishing this?

Mark, Based on your information, I'm posting here the working example that I came up with. Note that I had to add the NULL reset of the variable in order for the next group of statements to have a unique return code--rather than carry the previous return code forward.

Begin
    Declare l_RetCode   Int;
    Declare Continue Handler for Is_Duplicate, Missing_Keys, Missing_Rows
        Begin
            Set l_RetCode = SQLCODE;
        End;

    Set v_MsgText = 'ADD LOC Check Constraint failed.';
    Set v_SqlStmt = 'ALTER TABLE LIBRAT.APNVEND_RECORD_APNV_LOC_DATA ADD CONSTRAINT LIBRAT_APNVEND_RECORD_APNV_LOC_DATA_C1 CHECK ( APNV_LOC > 0 )';
    Execute Immediate v_SqlStmt;
    Set v_MsgText = 'ADD LOC Check Constraint rc=' || Coalesce(l_RetCode,0);
    Call UTL_FILE.PUT_LINE(v_LogHandle, v_MsgText);
    Set l_RetCode = NULL;
...snip...

Solution

Look at the example below.

--#SET TERMINATOR @
SET SERVEROUTPUT ON@

BEGIN
  DECLARE v_stmt VARCHAR(1000);
  DECLARE SQLCODE INT;
  DECLARE v_rc INT;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
    -- SQLCODE will be empty after each EXECUTE, if you comment the next line out
    SET v_rc = SQLCODE;
  END;
  
  SET v_rc = NULL, v_stmt = 'CREATE TABLE ERR1(I IN)';
  EXECUTE IMMEDIATE v_stmt;
  CALL DBMS_OUTPUT.PUT_LINE('"' || v_stmt || '" rc: ' || COALESCE(v_rc, 0));

  SET v_rc = NULL, v_stmt = 'DROP TABLE CORR1';
  EXECUTE IMMEDIATE v_stmt;
  CALL DBMS_OUTPUT.PUT_LINE('"' || v_stmt || '" rc: ' || COALESCE(v_rc, 0));

  SET v_rc = NULL, v_stmt = 'CREATE TABLE CORR1(I INT)';
  EXECUTE IMMEDIATE v_stmt;
  CALL DBMS_OUTPUT.PUT_LINE('"' || v_stmt || '" rc: ' || COALESCE(v_rc, 0));
END
@

You need a CONTINUE HANDLER FOR SQLEXCEPTION , not CONTINUE HANDLER FOR NOT FOUND (your not_found is strange - it's illegal), if you want to continue after errors.
Notice, that empty continue handler really clears SQLCODE / SQLSTATE variables, so you need to save their values with the 1-st statement of the handler as in the example, if SQLCODE is the only variable needed or use the GET DIAGNOSTICS statement (it doesn't reset SQLCODE) as the 1-st statement, if you need both SQLCODE / SQLSTATE values.

So, you get the following output on the 1-st call of this script:

"CREATE TABLE ERR1(I IN)" rc: -204
"DROP TABLE CORR1" rc: -204
"CREATE TABLE CORR1(I INT)" rc: 0

and on the 2-nd (subsequent) call:

"CREATE TABLE ERR1(I IN)" rc: -204
"DROP TABLE CORR1" rc: 0
"CREATE TABLE CORR1(I INT)" rc: 0