DB2 SQL PL – MESSAGE_TEXT

When you want SP to return message_text and you also want to handle error code as per your choice, follow this simple example.

CREATE PROCEDURE myproc(
        OUT v_msg VARCHAR(300))
LANGUAGE SQL
BEGIN
   DECLARE V_ERROR INTEGER DEFAULT 0;
   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
      BEGIN
         GET DIAGNOSTICS EXCEPTION 1 V_MSG = MESSAGE_TEXT;
         SET V_ERROR = SQLCODE;
      END;

   SET v_msg = '';

   -- DO SOME SQL that throws the error and do conditional processing but want to see detailed 
   -- error message so that you can see what went wrong.

   IF V_ERROR < 0 THEN
      RETURN -6786; -- Code that want to process later on return through application
   END IF;
END

The OUT parameter v_msg will give you detailed error message that DB2 will throw and you handled error as per your condition code.