Stored (SQL) procedure error handling technique

[William Washington III <w.washington3@xxxxxxxxxxxxx>]

Hi All,

What is the "tried and true" way of handling errors in SQL procedures created
via the Run SQL Statement program in iSeries Navigator?

For instance, I have a stored proc as follows:

CREATE PROCEDURE myproc

LANGUAGE SQL MODIFIES SQL DATA

BEGIN
   DECLARE Record_not_found CONDITION FOR '02000';
   DECLARE File_not_found CONDITION FOR '42704';

   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
      BEGIN
      END;

   DECLARE CONTINUE HANDLER FOR Record_not_found
      BEGIN
      END;

   DECLARE CONTINUE HANDLER FOR File_not_found
      BEGIN
      END;
   
   DELETE FROM file_a;
   INSERT INTO file_a SELECT * from file_b;
   DELETE FROM file_c;
   INSERT INTO file_c SELECT * from file_d;
END;


How do I trap for file_a or file_c having zero records?  Or if file_a or file_c
does not exist?

This is a very simplified example.  Assume that the file_a and file_c has
identical attributes of file_b and file_d respectively.  Does anyone have this
down to a science yet?

Thanks in advance...

William