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
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1