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

Returning procedure results in parameters

Procedures return results to the calling environment in one of the following ways:

• Individual values are returned as OUT or INOUT parameters.

• Result sets can be returned.

• A single result can be returned using a RETURN statement.

This section describes how to return results from procedures as parameters.

The following procedure on the sample database returns the average salary of employees as an OUT parameter.

CREATE PROCEDURE AverageSalary( OUT avgsal
   NUMERIC (20,3) )
BEGIN
   SELECT AVG( salary )
   INTO avgsal
   FROM employee;
END

To run this procedure and display its output (SQL)

1. Connect to the sample database from Interactive SQL with a user ID of DBA and a password of SQL. For more information about connecting, see Connecting to a Database.

2. In the SQL Statements pane, type the above procedure code.

3. Create a variable to hold the procedure output. In this case, the output variable is numeric, with three decimal places, so create a variable as follows:

   CREATE VARIABLE Average NUMERIC(20,3)

4. Call the procedure using the created variable to hold the result:

   CALL AverageSalary(Average)

   If the procedure was created and run properly, the Interactive SQL Messages pane does not display any errors.

5. Execute the SELECT Average statement to inspect the value of the variable.

   Look at the value of the output variable Average. The Results tab in the Results pane displays the value 49988.623 for this variable, the average employee salary.