In an SQL procedure, execute a GET DIAGNOSTICS statement to determine how many rows were updated.
CREATE PROCEDURE sqlprocg (IN deptnbr VARCHAR(3)) LANGUAGE SQL BEGIN DECLARE SQLSTATE CHAR(5); DECLARE rcount INTEGER; UPDATE CORPDATA.PROJECT SET PRSTAFF = PRSTAFF + 1.5 WHERE DEPTNO = deptnbr; GET DIAGNOSTICS rcount = ROW_COUNT; /* At this point, rcount contains the number of rows that were updated. */ END
Within an SQL procedure, handle the returned status value from the invocation of a stored procedure called TRYIT. TRYIT could use the RETURN statement to explicitly return a status value or a status value could be implicitly returned by the database manager. If the procedure is successful, it returns a value of zero.
CREATE PROCEDURE TESTIT () LANGUAGE SQL A1: BEGIN DECLARE RETVAL INTEGER DEFAULT 0; ... CALL TRYIT GET DIAGNOSTICS RETVAL = RETURN_STATUS; IF RETVAL <> 0 THEN ... LEAVE A1; ELSE ... END IF; END A1
In an SQL procedure, execute a GET DIAGNOSTICS statement to retrieve the message text for an error.
CREATE PROCEDURE divide2 ( IN numerator INTEGER, IN denominator INTEGER, OUT divide_result INTEGER, OUT divide_error VARCHAR(70) ) LANGUAGE SQL BEGIN DECLARE CONTINUE HANDLER FOR SQLEXCEPTION GET DIAGNOSTICS EXCEPTION 1 divide_error = MESSAGE_TEXT; SET divide_result = numerator / denominator; END;
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.