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. The following procedure returns a result set containing the salary for each employee in a given department:

CREATE PROCEDURE SalaryList (IN department_id INT)
RESULT ( "Employee ID" INT, "Salary" NUMERIC(20,3) )
BEGIN
    SELECT emp_id, salary
    FROM employee 
    WHERE employee.dept_id = department_id;
END

If Interactive SQL calls this procedure, the names in the RESULT clause are matched to the results of the query and used as column headings in the displayed results.

To test this procedure from Interactive SQL, you can call it, specifying one of the departments of the company in the CALL statement. The results appear in the Interactive SQL Results pane.

Example

To list the salaries of employees in the R & D department (department ID 100), type the following:

CALL SalaryList (100)

Employee ID	Salary
102	45700.000
105	62000.000
160	57490.000
243	72995.000
247	48023.690

Interactive SQL can only return multiple result sets if you have this option enabled on the Commands tab of the Options dialog. For more information, see “Returning multiple result sets from procedures”.

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