If the CREATE PROCEDURE or REPLACE PROCEDURE statement for the external stored procedure specifies the DYNAMIC RESULT SETS clause, the external stored procedure can create dynamic result sets that are returned to the client application or to the caller of the external stored procedure upon completion of the external stored procedure.
A result set is a set of rows that is the result of a SELECT statement that the external stored procedure executes. An external stored procedure can create up to 15 result sets, depending on the specification of the DYNAMIC RESULT SETS clause.
To create a result set from an external stored procedure:
- Follow the basic steps for defining C or C++ external stored procedures that use CLIv2 to directly execute SQL, as described in Overall Procedure.
- Submit a single SELECT statement, setting the following options of the DBCAREA.
| Option | Value | Description |
|---|---|---|
| Stored Procedure Return Result | 2 | Return the response to the SELECT statement to the client application. |
| 3 | Return the response to the SELECT statement to the caller of the external stored procedure. | |
| 4 | Return the response to the SELECT statement to the client application and to the external stored procedure. | |
| 5 | Return the response to the SELECT statement to the caller of the external stored procedure and to the external stored procedure. | |
| Keep Response | 'Y' | The set of rows returned follows the rules for NO SCROLL cursors. |
| 'P' | The set of rows returned follows the rules for SCROLL cursors. |
Here is a code excerpt for an external stored procedure that submits a SELECT statement and creates a result set to return to the caller of the external stored procedure:
... memset(str1, ' ', 100); sprintf(str1, "SELECT * FROM UserIds WHERE UName = %s;", A_Name); dbcarea.func = DBFIRQ; dbcarea.req_ptr = str1; dbcarea.req_len = strlen(str1); dbcarea.change_opts = 'Y'; dbcarea.SP_return_result = 3; dbcarea.keep_resp = 'Y'; DBCHCL(&result, cntxt, &dbcarea); ...
After the external stored procedure completes execution, the database returns the result sets created by the external stored procedure to the client or caller.