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.
Example 1
|
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:
|
1 2 3 |
CREATE PROCEDURE proc2 AS SELECT emp_name FROM employee SELECT part_name FROM parts |
PowerBuilder provides access to both result sets:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
// 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:
|
1 |
FETCH emp_proc2 INTO :part_name_var; |
you coded it as:
|
1 |
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