I'm writing SQL Stored Procedures on DB2 8.2 on Linux and trying to return an integer result for each of them showing the execution status to the caller.
Now, I'm able to use RETURN v_status at the end of the procedure body, but I also want to catch all the SQL exceptions and for this purpose I have defined an EXIT HANDLER for the procedure body.
When I try to put the same RETURN v_status in the handler's body I get a compilation error. It seems that I can't use a RETURN statement in the EXIT HANDLER.
Any idea, thanks?
The procedure is something like this:
CREATE PROCEDURE xxx
DECLARE EXIT HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS EXCEPTION 1 SQLERRMSG = MESSAGE_TEXT;
RETURN RETCODE; <- This will raise a compilation error
RETURN RETCODE; <- This works