Retrieving the procedure status

When an SQL procedure returns control to the calling program, it also returns the procedure status. The status is an integer value that indicates the success of the procedure.

To retrieve the procedure status, perform one of the following actions in the calling program:

• Issue the GET DIAGNOSTICS statement with the DB2_RETURN_STATUS item. The specified host variable in the GET DIAGNOSTICS statement is set to one of the following values:

  0

  This value indicates that the procedure returned with an SQLCODE that is greater or equal to zero. You can access the value directly from the SQLCA by retrieving the value of SQLERRD(1). For C applications, retrieve SQLERRD[0].

  -1

  This value indicates that the procedure returned with an SQLCODE that is less than zero. In this case, the SQLERRD(1) value in the SQLCA is not set. Db2 returns -1 only.

  n

  Any value other than 0 or -1 is the return value that was explicitly set in the procedure with the RETURN statement.

  For example, the following SQL code creates an SQL procedure that is named TESTIT, which calls another SQL procedure that is named TRYIT. The TRYIT procedure returns a status value. The TESTIT procedure retrieves that value with the DB2_RETURN_STATUS item of the GET DIAGNOSTICS statement.

  CREATE PROCEDURE TESTIT ()
    LANGUAGE SQL
    A1:BEGIN
    DECLARE RETVAL INTEGER DEFAULT 0;
      ...
      CALL TRYIT;
      GET DIAGNOSTICS RETVAL = DB2_RETURN_STATUS;
      IF RETVAL <> 0 THEN
        ...
        LEAVE A1;
      ELSE
        ...
      END IF;
    END A1

• Retrieve the value of SQLERRD(1) in the SQLCA. For C applications, retrieve SQLERRD[0]. This field contains the integer value that was set by the RETURN statement in the SQL procedure. This method is not applicable if the status was set by Db2.