SAP Adaptive
Server Enterprise 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.
For example:
|
1 |
FETCH emp_proc INTO :emp_name_var; |
Note
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.
Example 1
Database stored procedures can return multiple result sets. Assume
you define a database stored procedure proc2 as:
|
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 41 |
// 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; // 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. if SQLCA.sqlcode = 100 then FETCH emp_proc2 INTO :part_name_var; end if // 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 // Close the procedure. CLOSE emp_proc2; |
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.