Microsoft SQL Server FETCH

To access rows returned in a result set, you use the FETCH statement the same way you use it for cursors. The FETCH statement can be executed after any EXECUTE statement that refers to a procedure that returns a result set.

FETCH emp_proc INTO :emp_name_var;

You can use this FETCH statement only to access values produced with a SELECT statement in a database stored procedure. You cannot use the FETCH statement to access computed rows.

Database stored procedures can return multiple result sets. Assume you define a database stored procedure proc2 as follows:

CREATE PROCEDURE proc2 AS
	SELECT emp_name FROM employee
	SELECT part_name FROM parts

PowerBuilder provides access to both result sets:

// Declare the procedure.
DECLARE emp_proc2 PROCEDURE FOR proc2;

// Declare some variables to hold results.
string    emp_name_var
string    part_name_var

// Execute the stored procedure.
EXECUTE emp_proc2;

// Fetch the first row from the first result
// set.
FETCH emp_proc2 INTO :emp_name_var;

// Loop through all rows in the first result
// set.
DO WHILE sqlca.sqlcode = 0

// Fetch the next row from the first result set.
	FETCH emp_proc2 INTO :emp_name_var;
LOOP

// At this point we have exhausted the first
// result set. After this occurs, 
// PowerBuilder notes that there is another
// result set and internally shifts result sets. 
// The next FETCH executed will retrieve the 
// first row from the second result set.
// Fetch the first row from the second result
// set.
	FETCH emp_proc2 INTO :part_name_var;

// Loop through all rows in the second result
// set.
DO WHILE sqlca.sqlcode = 0

// Fetch the next row from the second result
// set.
	FETCH emp_proc2 INTO :part_name_var;
LOOP

The result sets that will be returned when a database stored procedure executes cannot be determined at compile time. Therefore, you must code FETCH statements that exactly match the format of a result set returned by the stored procedure when it executes.

In the preceding example, if instead of coding the second fetch statement as:

FETCH emp_proc2 INTO :part_name_var;

you coded it as:

FETCH emp_proc2
	INTO :part_var1,:part_var2,:part_var3;

the statement would compile without errors. But an execution error would occur: the number of columns in the FETCH statement does not match the number of columns in the current result set. The second result set returns values from only one column.