CONTINUE HANDLER in Stored Procedure

Database
Enthusiast

CONTINUE HANDLER in Stored Procedure

I am using a CONTINUE HANDLER as part of my stored procedure to manage Dropping of Indexes.

I do not want the SP to error out becuase an Index doesn't exist as I am just wanting to drop it any way.

The CONTINUE HANDLER does just fine if there is 1 index missing if there are multiple then the handler is no longer managing the process and the SP errors out with a message of 'Index doesn't exist'

DECLARE CONTINUE HANDLER FOR SQLSTATE VALUE '52004'
DDLERROR: BEGIN
INSERT INTO NSBI_TDB.ETL_DEBUG VALUES(CURRENT_TIMESTAMP(0), c_PROC_NM, 'INDEX0 DROP',SQLSTATE);
END DDLERROR;

DROP INDEX IX_NSBI_WRK_TH_1 ON TEST_WORK_DB.TEST;
DROP INDEX IX_NSBI_WRK_TH_2 ON TEST_WORK_DB.TEST;
DROP INDEX IX_NSBI_WRK_TH_3 ON TEST_WORK_DB.TEST;
DROP INDEX IX_NSBI_WRK_TH_4 ON TEST_WORK_DB.TEST;

I have dropped all th indexes ahead of time to see if the error handling would work. If 1 index is missing then it runs just fine and continues with the rest of the SP. However, if 2 or more are missing then it just errors out.

What I am seeing for the SQLSTATE value when the 2nd drop index statement fails is a value of '00000' instead of 52004.
1 REPLY
Enthusiast

Re: CONTINUE HANDLER in Stored Procedure

I think I found the answer to my own question:
This is what I did and it appears to be working

DDLERROR: BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE VALUE '52004'
BEGIN
RESIGNAL SQLSTATE '52004';

DROP INDEX IX_NSBI_WRK_TH_1 ON TEST_WORK_DB.test;
DROP INDEX IX_NSBI_WRK_TH_2 ON TEST_WORK_DB.test;
DROP INDEX IX_NSBI_WRK_TH_3 ON TEST_WORK_DB.test;
DROP INDEX IX_NSBI_WRK_TH_4 ON TEST_WORK_DB.test;
END;
END DDLERROR;

If there are other suggestions for handling this please feel free to include them as well.