Contents Index Returning result sets from procedures Returning variable result sets from procedures

ASA SQL User's Guide
  Using Procedures, Triggers, and Batches
    Returning results from procedures

Returning multiple result sets from procedures

Before Interactive SQL can return multiple result sets, you need to enable this option on the Results tab of the Options dialog. By default, this option is disabled. If you change the setting, it takes effect in newly created connections (such as new windows).

To enable multiple result set functionality

  1. Choose Tools > Options.

  2. In the resulting Options dialog, click the Results tab.

  3. Select the Show Multiple Result Sets checkbox.

After you enable this option, 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 

Contents Index Returning result sets from procedures Returning variable result sets from procedures