A stored procedure might return a code that provides information. For example, it might indicate whether the stored procedure was successful or whether it encountered an error condition. The following example of ABL code runs the stored procedure pcust. It uses the PROC–STATUS function and the CLOSE STORED–PROC statement to retrieve the return code and assign the value to the variable stat. The meaning of this return code is defined by the underlying data source:
/* Return status */
DEFINE VARIABLE iStat AS INTEGER NO-UNDO.
RUN STORED-PROCEDURE pcust (20, OUTPUT 0, OUTPUT 0).
FOR EACH proc-text-buffer:
DISPLAY proc-text-buffer.
END.
CLOSE STORED-PROC pcust iStat = PROC-STATUS.
DISPLAY pcust.orders pcust.states stat.
Note: When the DataServer sends a request to execute a native stored procedure, ODBC assumes that a return code will be received. However, DB2 UDB native stored procedures do not directly support return codes. Instead, DB2 UDB simulates the use of a return code for ODBC by inserting an additional output parameter as the first parameter in your SQLDA structure parameter list. This additional parameter serves as a return value to the caller. The ODBC DataServer can read a return value properly as long as the additional parameter is accommodated for in your DB2 UDB native stored procedure. Place the value you would like to have output as your return code in this first parameter. It can then be received in Progress with the PROC–STATUS option just as you would for any other native stored procedure environment. You do not need to register your stored procedure in the DB2 UDB database with an additional parameter and doing so would elicit a signature violation at execution time.
