CALL statement

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

Syntax

You can implement the CALL statement in ESQL or externally as a database stored procedure.

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>.

Start of changeEach broker schema provides a unique function and procedure symbol space.End of change

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.

Start of changeIf 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.End of change

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

Related tasks
Developing ESQL
Invoking stored procedures

Related reference
Syntax preference
ESQL statements
CREATE PROCEDURE statement
BROKER SCHEMA statement
PATH clause