SQLSTATE 02000 for a Cursor Loop - How do I trap other NOT FOUND?

[CurtisJM]

I am developing stored procedures in SQL. Cursor operation NOT FOUND conditions are being handled with a CONTINUE HANDLER. The problem I have is that I have SQL Statements that are also potentially raising SQLSTATE 02000 - ones that might try an assignment to a variable with a SET or a SELECT INTO (the later of which is raising SQLSTATE 02000 if the WHERE clause is not met) and then more importantly, INSERT INTO statements that again might produce an empty result set. When these statements raise SQLSTATE 02000 my continue handler is called and the cursor loop exits.

I know that I can do existence tests before, but such a test is then followed by the exact same statement if it is true... that seems quite inefficient. Does anyone have any suggestions on other approaches?

 

[CurtisJM]

One way that seems to work - but I have not tested enough yet - is to put my sql statement that might operate on an empty result set (or produce one) in a BEGIN - END block with a new condition handler... but this still seems a bit less efficient. For example - this is the beginning of my cursor loop and a block ...

OPEN c1;
FETCH c1 INTO ResultID, CT;
WHILE (no_rows = 0) DO
<... some other work here>
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
SET dummy = 0; -- just want a no-op...
INSERT INTO SESSION.TST (SAN) SELECT SAN from S_SUBJ_CONC WHERE SAN = ResultID;
END ;
<... > rest of loop...