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.
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 avgsal NUMERIC(20,3) ) BEGIN SELECT AVG( Salary ) INTO avgsal 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:
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.
|Discuss this page in DocCommentXchange.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|