CALL statement

Invokes a procedure.

Syntax

Specify argument by position

[variable = ] CALL procedure-name ( [ expression, ... ] ) [ AS USER { string | variable } IDENTIFIED BY { string | variable } ]

Specify argument by keyword format

[variable = ] CALL procedure-name ( [ parameter-name = expression, ... ] ) [ AS USER { string | variable } IDENTIFIED BY { string | variable } ]

Parameters

AS USER ... IDENTIFIED BY clause This optional clause calls a procedure or function as a different user. The database server verifies that the user ID and password provided are valid, and then executes the procedure or function as the specified user. The invoker of the procedure is the specified user. Upon exiting the procedure or function, the user context is restored to its original state.
Note All string values must be enclosed in single quotes; otherwise the database server interprets them as variable names.

Remarks

The CALL statement invokes a procedure that was previously created with a CREATE PROCEDURE statement. When the procedure completes, any INOUT or OUT parameter value is copied back.

Note The AS USER ... IDENTIFIED BY clause only applies to the CALL statement and is not supported for procedures in the FROM clause or functions in the select list.

If you use this statement in a procedure, do not specify the password (IDENTIFIED BY clause) as a string literal because the definition of the procedure is visible in the SYSPROCEDURE system view. For security purposes, specify the password using a variable that is declared outside of the procedure definition.

Database-scope variables must not be specified as INOUT or OUT parameters when calling a procedure.

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.

Note

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:

DECLARE varname INT;
SET varname=test( );

Privileges

The user calling the procedure, or the user specified by the AS USER ... IDENTIFIED BY clause, must be the owner of the procedure, or have one of the following privileges:

EXECUTE privilege on the procedure
EXECUTE ANY PROCEDURE system privilege

Side effects

None.

Standards

ANSI/ISO SQL Standard
Core Feature. The use of the RETURN statement to return a value from a stored procedure is not part of the standard; the ANSI/ISO SQL Standard supports return values only for SQL-invoked functions, not for procedures. Default values for stored procedure arguments are not in the standard.

Example

The following 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( GROUPO.SalesOrders.ID )
   INTO Orders
   FROM GROUPO.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 SYS.DUMMY
go

The following example uses the AS USER...IDENTIFIED BY clause to execute a procedure as a different user:

Create three sample users by executing the following statements:

CREATE USER u IDENTIFIED BY pwdforu;
CREATE USER u1 IDENTIFIED BY pwdforu1;
CREATE USER u2 IDENTIFIED BY pwdforu2;

Create and populate two tables by executing the following statements:

CREATE TABLE u1.t1 (c1 INT);
CREATE TABLE u2.t2 (c1 INT);
INSERT INTO u1.t1 VALUES(1);
INSERT INTO u2.t2 VALUES(2);
COMMIT;

Create two stored procedures by executing the following statements:

CREATE PROCEDURE u1.p1( OUT ret INT, OUT inv_user CHAR(128), OUT exec_user CHAR(128) )
SQL SECURITY INVOKER
BEGIN
   SELECT c1 INTO ret FROM t1;
   SET inv_user = invoking_user;
   SET exec_user = executing_user;
END;

CREATE PROCEDURE u2.p2( OUT ret INT, OUT inv_user CHAR(128), OUT exec_user CHAR(128) )
SQL SECURITY DEFINER
BEGIN
   SELECT c1 INTO ret FROM t2;
   SET inv_user = invoking_user;
   SET exec_user = executing_user;
END;

Create a third procedure that calls the first procedure as user u1, using string values in the AS USER...IDENTIFIED BY clause:

CREATE PROCEDURE u.p1( OUT ret INT, OUT inv_user CHAR(128), OUT exec_user CHAR(128) )
SQL SECURITY DEFINER
BEGIN
   CALL u1.p1 ( ret, inv_user, exec_user ) AS USER 'u1' IDENTIFIED BY 'pwdforu1';
END;

Create a fourth procedure that calls the second procedure as user u, using variable values in the AS USER...IDENTIFIED BY clause:

CREATE PROCEDURE u.p2( IN u CHAR(128), IN p CHAR(128), OUT ret INT, OUT inv_user CHAR(128), OUT exec_user CHAR (128) )
SQL SECURITY DEFINER
BEGIN
   CALL u2.p2( ret, inv_user, exec_user ) AS USER u IDENTIFIED BY p;
END;

Now, if user u logs in and executes CALL u1.p1( ret, inv_user, exec_user ) or CALL u2.p2( ret, inv_user, exec_user ), then user u has permission denied since user u does not have permission to execute u1.p1 or u2.p2. However, user u is able to execute the following procedure:
```
CALL u1.p1( ret, inv_user, exec_user ) AS USER 'u1' IDENTIFIED BY 'pwdforu1';
```
Both the inv_user and exec_user are returned as u1 even though u1.p1 is a SQL SECURITY INVOKER procedure. This is because the database server executes u1.p1 as user u1, even though the user logged in is user u. Because the procedure executes as though u1 is calling it, it can access the non-fully qualified table t1, since u1.t1 exists.

Similarly, u is able to execute the following procedure:
```
CALL u2.p2( ret, inv_user, exec_user ) AS USER uvar IDENTIFIED BY pvar;
```
Here, uvar is a variable that contains the value 'u2' and pvar is a variable that contains the value 'pwdforu2'. The procedure executes without error and the inv_user and exec_user both come back as u2 and the procedure can access the non-fully qualified table t2.
If user u executes the following two procedures, then both calls succeed since the database server accepts the AS USER...IDENTIFIED BY clause nested within other stored procedures:
```
CALL u.p1( ret, inv_user, exec_user );
```
```
CALL u.p2( 'u2', 'pwdforu2', ret, inv_user, exec_user );
```