Filter
Top Products
Returning results from procedures
250
• When testing this procedure, DBISQL opens a cursor to handle the results.
The cursor is left open following the SELECT statement, in case the
procedure returns more than one result set. You should type RESUME to
complete the procedure and close the cursor.
Returning multiple result sets from procedures
A procedure can return more than one result set to the calling environment. If
a RESULT clause is employed, the result sets must be compatible: they must
have the same number of items in the SELECT lists, and the data types must
all be of types that can be automatically converted to the data types listed in the
RESULT list.
The following procedure lists the names of all employees, customers, and
contacts listed in the database:
CREATE PROCEDURE ListPeople()
RESULT ( lname CHAR(36), fname CHAR(36) )
BEGIN
SELECT emp_lname, emp_fname
FROM employee;
SELECT lname, fname
FROM customer;
SELECT last_name, first_name
FROM contact;
END
Notes
To test this procedure in DBISQL, enter the following statement:
CALL ListPeople ()
You must enter a RESUME statement after each of the three result sets is
displayed in the DBISQL Data window to continue, and then complete, the
procedure.
Returning variable result sets from procedures
The RESULT clause is optional in procedures. Omitting the result clause
allows you to write procedures that return different result sets, with different
numbers or types of columns, depending on how they are executed.