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 method for returning multiple result sets differs for dbisql and dbisqlc.

Before dbisql can return multiple result sets, you need to enable this option on the Commands tab of the Options dialog. By default, this option is disabled. If you change the setting, you must make it permanent and either start a new connection to the database or restart dbisql for the new setting to take effect.

1. Choose Tools > Options.

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

3. Select the Show Multiple Result Sets check box and click Make Permanent.

4. Do one of the following:

   Click File > New Window and enter connection information, or

   Restart dbisql.

For dbisqlc, you do not enable multiple result sets in advance. Instead, you call the procedure, and after each result set is displayed in the dbisqlc data window you must enter a RESUME statement to continue, and then complete, the procedure.

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

To test this procedure in either dbisql or dbisqlc, enter the following statement:

CALL ListPeople () 

• In dbisql, if you have previously enabled multiple result sets, all three result sets appear automatically in the Results pane. Otherwise, you see only the first result set.

• In dbisqlc, to display all three result sets, enter a RESUME statement after each one is displayed. You can also do this in a batch file. For example, you could create a batch file called listresults with the following contents:

  call ListPeople;
  resume;
  resume;
  resume;
  

  Then call the batch file:

  dbisqlc -q -c "uid=DBA;pwd=SQL"  
  read listresults.sql > listresults.out