Returning Result Sets from Stored Procedures

Result Sets from Stored Procedures

In addition to returning output parameters, stored procedures have a feature by which a result table associated with a cursor opened in the stored procedure (called a result set) can be returned to the application issuing the CALL statement. That application can then issue fetch requests to read the rows of the result set cursor. Whether a result set gets returned depends on the returnability attribute of the cursor. The cursor's returnability attribute can be explicitly given in the DECLARE CURSOR statement or it can be defaulted. The SET RESULT SETS statement also allows for an indication of where the result sets should be returned (see Example 2: Call a stored procedure which returns a result set from a nested procedure). By default, cursors which are opened in a stored procedure are defined to have a returnability attribute of RETURN TO CALLER. To return the result set associated with the cursor to the application which called the outermost procedure in the call stack, the returnability attribute of RETURN TO CLIENT is specified on the DECLARE CURSOR statement. This will allow inner procedures to return result sets when the application calls nested procedures. For cursors whose result sets are never to be returned to caller or client, the returnability attribute of WITHOUT RETURN is specified on the DECLARE CURSOR statement.

There are many cases where opening the cursor in a stored procedure and returning its result set provides advantages over opening the cursor directly in the application. For instance, security to the tables referenced in the query can be adopted from the stored procedure so that users of the application do not need to be granted direct authority to the tables. Instead, they are given authority to call the stored procedure, which is compiled with adequate authority to access the tables. Another advantage to opening the cursors in the stored procedure is that multiple result sets can be returned from a single call to the stored procedure, which can be more efficient that opening the cursors separately from the calling application. Additionally, each call to the same stored procedure may return a different number of result sets, providing some application versatility.

The interfaces that can work with stored procedure result sets include JDBC, CLI, and ODBC. An example on how to use these API interfaces for working with stored procedure result sets is included in the following examples:

* Example 1: Call a stored procedure which returns a single result set
* Example 2: Call a stored procedure which returns a result set from a nested procedure

Note:
See Code disclaimer information for information pertaining to code examples.