Contents Index Returning procedure results in parameters Introduction to user-defined functions

ASA SQL User's Guide
  Using Procedures, Triggers, and Batches
    Introduction to procedures

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 Interactive SQL, the results appear on the Results tab in the 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
105 62000
160 57490
243 72995
... ...

Interactive SQL can only return multiple result sets if you have this option enabled on the Results tab of the Options dialog. Each result set appears on a separate tab in the Results pane.

For more information, see Returning multiple result sets from procedures.


Contents Index Returning procedure results in parameters Introduction to user-defined functions