Using Interactive SQL, connect to the SQL Anywhere sample database as the DBA.

In the SQL Statements pane, type the following to create a procedure (AverageSalary) that returns the average salary of employees as an OUT parameter:

CREATE PROCEDURE AverageSalary( OUT average_salary NUMERIC(20,3) )
BEGIN
   SELECT AVG( Salary )
   INTO average_salary
   FROM Employees;
END;

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);

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.

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.