Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.0 » SQL Anywhere Server - SQL Usage » Stored procedures and triggers » 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 window. 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 (Interactive SQL)
  1. Connect to the database as a user with DBA authority.

  2. In Interactive SQL, choose Tools » Options.

  3. Click SQL Anywhere.

  4. On the Results tab, select Show All Result Sets.

  5. Click OK.

 To enable multiple result set functionality (SQL)
  1. Connect to the database as a user with DBA authority.

  2. Run the following:

    SET OPTION isql_show_multiple_result_sets=On

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.

 Example