Use this statement to invoke a procedure.
[variable = ] CALL procedure-name ( [ expression, ... ] )
[variable = ] CALL procedure-name ( [ parameter-name = expression, ... ] )
The CALL statement invokes a procedure that has been previously created with a CREATE PROCEDURE statement. When the procedure completes, any INOUT or OUT parameter value is copied back.
The argument list can be specified by position or by using keyword format. By position, the arguments will match up with the corresponding parameter in the parameter list for the procedure. By keyword, the arguments are matched up with the named parameters.
Procedure arguments can be assigned default values in the CREATE PROCEDURE statement, and missing parameters are assigned the default value. If no default is set, and an argument is not provided, an error is given.
Inside a procedure, a CALL statement can be used in a DECLARE statement when the procedure returns result sets. See Returning results from procedures.
Procedures can return an integer value (for example, as a status indicator) using the RETURN statement. You can save this return value in a variable using the equality sign as an assignment operator:
CREATE VARIABLE returnval INT; returnval = CALL proc_integer ( arg1 = val1, ... )
If the procedure being called returns an INT and the value is NULL, then the error status value, 0, is returned instead. There is no way to differentiate between this case and the case of an actual value of 0 being returned.
Must be the owner of the procedure, have EXECUTE permission for the procedure, or have DBA authority.
SQL/2003 Persistent Stored Module feature.
Call the ShowCustomers procedure. This procedure has no parameters, and returns a result set.
The following Interactive SQL example creates a procedure to return the number of orders placed by the customer whose ID is supplied, creates a variable to hold the result, calls the procedure, and displays the result.
CREATE PROCEDURE OrderCount (IN customer_ID INT, OUT Orders INT) BEGIN SELECT COUNT(SalesOrders.ID) INTO Orders FROM Customers KEY LEFT OUTER JOIN SalesOrders WHERE Customers.ID = customer_ID; END go -- Create a variable to hold the result CREATE VARIABLE Orders INT go -- Call the procedure, FOR customer 101 CALL OrderCount ( 101, Orders ) go -- Display the result SELECT Orders FROM DUMMY go
|Send feedback about this page via email or DocCommentXchange||Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0|