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.