The SET RESULT SETS statement specifies the
result sets that can be returned from a procedure.
This statement can
only be embedded in an application program or SQL procedure. It is
an executable statement that cannot be dynamically prepared. It is
not allowed in a Java™ or REXX procedure.
None required.
.-TO CALLER-. .-WITH RETURN--+-----------+-. | '-TO CLIENT-' | >>-SET RESULT SETS--+----------------------------+--------------> .-,----------------------------------------------------. V | >--+---+-ARRAY--host-structure-array--FOR--variable--ROWS-+-+-+->< | '-CURSOR--cursor-name------------------------------' | '-NONE-----------------------------------------------------'
For non-scrollable cursors, the result set consists of all rows from the current cursor position to the end of the result table. For scrollable cursors, the result set consists of all rows of the result table.
Identifies a cursor to be used to
define a result set that can be returned from a procedure. The cursor-name must
identify a declared cursor as explained in Description for the
DECLARE CURSOR statement. When the SET RESULT SETS statement is executed,
the cursor must be in the open state. It cannot be an allocated cursor.
The first structure in the array corresponds to the first row of the result set, the second structure in the array corresponds to the second row of the result set, and so on. In addition, the first value in the row corresponds to the first item in the structure, the second value in the row corresponds to the second item in the structure, and so on.
LOBs and XML cannot be returned
in an array when using DRDA®.
Only one array can be specified in a SET RESULT SETS statement, including any RETURN TO CLIENT array result sets from nested calls to procedures.
Specifies the number of rows in the
result set. The variable must be a numeric variable with zero
scale, and it must not include an indicator variable. It must not
be a global variable. The number of rows specified must be in the
range of 0 to 32767 and must be less than or equal to the dimension
of the host structure array.
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.
The following SET RESULT SETS statement specifies cursor X as the result set that will be returned when the procedure is called. For more information and complete examples showing the use of result sets from ODBC clients, see the IBM® i Access Family topic collection.
EXEC SQL SET RESULT SETS CURSOR X;