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

1. Connect to the sample database from Interactive SQL with a user ID of DBA and a password of SQL.

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 Interactive SQL Results pane displays the value 49988.623 for this variable, the average employee salary.