Testing SQLCODE and SQLWARN0 on Return from a Procedure

Upon return from an EXECUTE PROCEDURE call, the value of SQLCODE indicates the success of procedure execution itself, not of any individual statement in the procedure. After returning from an EXECUTE PROCEDURE statement, your application should test SQLCODE to determine if the proceedure was successful and SQLWARN0 for possible errors, warnings, and informational messages.

A non-zero SQLCODE is returned from procedure execution in the following situations:

The procedure was not executed at all. Possible reasons are that the procedure was not found or that the user does not have the appropriate authority.
An error occurred in the procedure when a WHENEVER SQLERROR STOP statement was active. Such an error causes the procedure to terminate, and the current transaction is rolled back.
An error occurred in evaluating an IF or WHILE condition or an expression in an assignment statement. Such an error causes the procedure to terminate, and previously executed statements are not rolled back (unless a severe error occurred).
An error occurred in copying output values to the user's host variables. For example, a null value might be returned, but a null indicator variable was not provided in the parameter list.

In all other cases, SQLCODE is 0 on return from a procedure, including cases in which errors occurred in a procedure and did not cause the procedure to stop. Messages for any errors from the last SQL statement executed by the procedure are available on return from the procedure by testing SQLWARN0 for a value of 'W' and using SQLEXPLAIN.

Checking for All Errors and Warnings

The following type of routine is recommended on return from an EXECUTE PROCEDURE statement in C:

   while (sqlca.sqlcode < 0 || sqlca.sqlwarn[0]=='W')

      do {

         EXEC SQL SQLEXPLAIN :SQLMessage;

		   printf("%s\n",SQLMessage);

		   }

The following is a similar routine in COBOL:

   IF SQLCODE IS NOT ZERO OR SQLWARN0 = "W" THEN

      PERFORM M100-DISPLAY-MESSAGE 

      UNTIL SQLCODE IS ZERO AND SQLWARN0 <> "W".



   [vellip]



   M100-DISPLAY-MESSAGE.

      EXEC SQL SQLEXPLAIN :SQLMESSAGE END-EXEC.

      DISPLAY SQLMESSAGE.

   M100-EXIT.

      EXIT.

This routine tests both SQLCODE and SQLWARN0 for the presence of error conditions, warnings, and messages of all kinds, including those generated by PRINT and RAISE ERROR statements and resulting from PRINTRULES being set on.

Testing SQLCODE and SQLWARN0 on Return from a Procedure

Technical documentation

» Table of Contents

» Index

Checking for All Errors and Warnings