Returning Procedure Results in Result Sets

In addition to returning results to the calling environment in individual parameters, procedures can return information in result sets. A result set is typically the result of a query.

See SQL Anywhere 11.0.1 > SQL Anywhere Server - SQL Usage > Stored Procedures and Triggers > Using procedures, triggers, and batches > Introduction to procedures > Returning procedure results in result sets.

Creating and Selecting from Temporary Tables

If a procedure dynamically creates and then selects the same temporary table within a stored procedure, you must use the EXECUTE IMMEDIATE WITH RESULT SET ON syntax to avoid Column not found errors.

For example:

CREATE PROCEDURE p1 (IN @t varchar(30))
   BEGIN
      EXECUTE IMMEDIATE 
      'SELECT * INTO #resultSet FROM ' || @t;
      EXECUTE IMMEDIATE WITH RESULT SET ON 
      'SELECT * FROM #resultSet';   END