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 match up with the corresponding
parameter in the parameter list for the procedure (DEFAULT can be used for an optional parameter). 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.
Subqueries and spatial method calls are not allowed as arguments to a stored procedure in a CALL statement.
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:
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.
Use of this statement to invoke a function is deprecated. If you have a function you want to call, consider using an assignment
statement to invoke the function and assign its result to a variable. For example:
Core feature. The use of the RETURN statement to return a value from a stored procedure is a vendor extension; SQL/2008
supports return values only for SQL-invoked functions, not for procedures. Default values for stored procedure arguments are
not supported in SQL/2008.
This 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)
KEY LEFT OUTER JOIN SalesOrders
WHERE Customers.ID = customer_ID;
-- Create a variable to hold the result
CREATE VARIABLE Orders INT
-- Call the procedure, FOR customer 101
CALL OrderCount ( 101, Orders )
-- Display the result
SELECT Orders FROM DUMMY