Example

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 CONDITION 1
            divide_error = MESSAGE_TEXT;
       SET divide_result = numerator / denominator;
    END;