Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

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.

• Procedures can return a single result using a RETURN statement.

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

The following procedure on the SQL Anywhere 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 Employees;
END;

To run this procedure and display its output (SQL)

1. Using Interactive SQL, connect to the SQL Anywhere sample database as the DBA. 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 tab does not display any errors.

5. To inspect the value of the variable, execute the following statement:

   SELECT Average;

   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.