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.

To create and run a procedure, and display its output (SQL)

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

  2. 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 avgsal NUMERIC(20,3) )
    BEGIN
       SELECT AVG( Salary )
       INTO avgsal
       FROM Employees;
    END;
  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.

See also