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

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Using Procedures, Triggers, and Batches » Introduction to procedures

Deleting procedures Next Page

Returning procedure results in parameters

Procedures return results to the calling environment in one of the following ways:

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) )
   SELECT AVG( Salary )
   INTO avgsal
   FROM Employees;
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:

  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.