Notes

Result sets are only returned if the procedure is directly called or if the procedure is a RETURN TO CLIENT procedure and is indirectly called from ODBC, JDBC, OLE DB, .NET, the SQL Call Level Interface, or the iSeries Access Family Optimized SQL API. For more information about result sets, see Result sets from procedures and WITH RETURN clause.

External procedures: There are three ways to return result sets from an external procedure:

When a result set is returned using an open cursor, the rows are returned starting with the current cursor position.

The RESULT SETS clause should be specified on the ALTER PROCEDURE (External), CREATE PROCEDURE (External) statement, or DECLARE PROCEDURE statement to return result sets from a procedure. The maximum number of result sets returned cannot be larger than the number specified on the ALTER PROCEDURE (External), CREATE PROCEDURE (External) statement, or DECLARE PROCEDURE statement.

SQL procedures: In order to return result sets from an SQL procedure, the procedure must be created with the RESULT SETS clause. Each cursor that is defined with the WITH RETURN clause that the procedure opens and leaves open when it returns identifies a result set.

When a result set is returned using an open cursor, the rows are returned starting with the current cursor position.

The RESULT SETS clause must be specified on the CREATE PROCEDURE (SQL) statement to return any result sets from an SQL procedure. The maximum number of result sets returned cannot be larger than the number specified on the CREATE PROCEDURE statement.