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.
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.