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 Server - SQL Usage > Stored procedures and triggers > Using procedures, triggers, and batches > Introduction to procedures > Returning procedure results in result sets.

Note: This reference points to SQL Anywhere documentation.

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