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 |
CREATE PROCEDURE proc2 AS<br> SELECT emp_name FROM employee<br> SELECT part_name FROM parts |
PowerBuilder provides access to both result sets:
|
1 |
// Declare the procedure.<br>DECLARE emp_proc2 PROCEDURE FOR proc2; |
|
1 |
// Declare some variables to hold results.<br>string    emp_name_var<br>string    part_name_var |
|
1 |
// Execute the stored procedure.<br>EXECUTE emp_proc2; |
|
1 |
// Fetch the first row from the first result<br>// set.<br>FETCH emp_proc2 INTO :emp_name_var; |
|
1 |
// Loop through all rows in the first result<br>// set.<br>DO WHILE sqlca.sqlcode = 0 |
|
1 |
// Fetch the next row from the first result set.<br> FETCH emp_proc2 INTO :emp_name_var;<br>LOOP |
|
1 |
// At this point we have exhausted the first<br>// result set. After this occurs, <br>// PowerBuilder notes that there is another<br>// result set and internally shifts result sets. <br>// The next FETCH executed will retrieve the <br>// first row from the second result set.<br>// Fetch the first row from the second result<br>// set.<br> FETCH emp_proc2 INTO :part_name_var; |
|
1 |
// Loop through all rows in the second result<br>// set.<br>DO WHILE sqlca.sqlcode = 0 |
|
1 |
// Fetch the next row from the second result<br>// set.<br> FETCH emp_proc2 INTO :part_name_var;<br>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<br> 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.