CALL statement

The CALL statement calls a routine that has been defined using the CREATE PROCEDURE or CREATE FUNCTION statement.

SYNTAX

You can use the CALL statement to invoke a routine defined in ESQL, Java, or a database. For further information on using Java, see External Java methods.

The CALL statement passes the parameters into the procedure in the order given to it. Parameters that have been defined as IN or INOUT are evaluated before the CALL is made, but parameters defined as OUT are always passed in as NULL parameters of the correct type. When the procedure has completed, any parameters declared as OUT or INOUT are updated to reflect any changes made to them during the procedure's execution. Parameters defined as IN are never changed during the cause of a procedure's execution.

When the optional brokerSchemaName parameter is not specified, the broker SQL parser searches for the named procedure using the algorithm described in the PATH statement (see the PATH clause).

When the brokerSchemaName parameter is specified, the broker SQL parser invokes the named procedure without first searching the path.

The optional brokerSchemaName parameter is not supported on WebSphere Business Integration Message Broker Version 2.1 and earlier brokers. All ESQL that is deployed to a Version 2.1, or earlier broker, must be defined in the same broker schema as the message flow, that is the <node schema>.

Each broker schema provides a unique function and procedure symbol space.

The INTO clause is used to store the return value from the invocation of a routine that has been specified with a RETURNS clause. The target can be an ESQL variable of a data type that matches the data type on the RETURNS clause or a dot-separated message reference. For example, both of the following ESQL statements are valid:
        CALL myProc1() INTO cursor;
        CALL myProc1() INTO OutputRoot.XML.TestValue1; 

A CALL to a routine defined with the CREATE FUNCTION statement is allowed, as long as the INTO keyword is specified.

The method of invocation must match the method of declaration; that is, if the RETURNS clause is specified on the CREATE PROCEDURE or CREATE FUNCTION statement, the INTO clause must also be specified. If the RETURNS clause is not specified on the CREATE PROCEDURE statement, the INTO clause must not be specified.

The EXTERNAL SCHEMA clause is allowed only if either:
  • The CALL is to a PROCEDURE that has the DATABASE option selected in the LANGUAGE clause, or
  • The External name is specified in the language database, that is, it is an implicit declaration.
If you do not have the LANGUAGE clause and an external schema name, you are working in ESQL.
Notes:
  1. If a procedure reference is ambiguous, that is, there are two procedures with the same name in different broker schemas, and the reference is not qualified by the optional brokerSchemaName, the Eclipse tool set generates a Tasks view error that you must correct to deploy the ambiguous code.
  2. Procedure overloading is not supported for either internal procedures or external stored procedures. (An overloaded procedure is one that has the same name as another procedure in the same broker schema (internal procedure) or database schema (external procedure) which has a different number of parameters, or parameters with different types.) If the broker detects that a procedure has been overloaded, it raises an exception.

If you are calling a stored procedure name that has been defined with a wildcard database schema name specified as a single percent character in the CREATE PROCEDURE statement, you must also include the EXTERNAL SCHEMA clause and provide the databaseSchemaName on this statement. In all other cases, the clause is not required and an exception is thrown if it is specified.

The built-in functions are, in effect, placed in a predefined broker schema called SQL. Therefore, user-defined module level functions of the same name take precedence over these built-in functions.

For an example of the use of CALL, see the examples in the CREATE PROCEDURE statement.

Related concepts
ESQL overview
Related tasks
Developing ESQL
Invoking stored procedures
Related reference
Syntax diagrams: available types
ESQL statements
CREATE PROCEDURE statement
BROKER SCHEMA statement
PATH clause