Sybase 12.4.2 manual Returning multiple result sets from procedures, Procedure, 250

Returning results from procedures

•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.

250