Connecting to Your Database
Using Embedded SQL with Microsoft SQL Server

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.

Example 1

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.

Example 2

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.

See also

Microsoft SQL Server FETCH NEXT

Microsoft SQL Server FETCH FIRST, FETCH PRIOR, and FETCH LAST