Before Interactive SQL can return multiple result sets, you need to enable this option on the Results tab of the Options window. By default, this option is disabled. If you change the setting, it takes effect in newly created connections (such as new windows).
Connect to the database as a user with DBA authority.
In the left pane, select the database and choose File » Open Interactive SQL.
In Interactive SQL, choose Tools » Options.
Click SQL Anywhere.
On the Results tab, select Show All Result Sets.
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 ( Surname CHAR(36), GivenName CHAR(36) ) BEGIN SELECT Surname, GivenName FROM Employees; SELECT Surname, GivenName FROM Customers; SELECT Surname, GivenName FROM Contacts; END;
To test this procedure and view multiple result sets in Interactive SQL, enter the following statement in the SQL Statements pane:
CALL ListPeople ();
|Discuss this page in DocCommentXchange.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|