Micro Focus Enterprise Developer 3.0 for Eclipse > Programming > Data Access > Database Access > Reference > OpenESQL > Embedded SQL Statements > CALL

CALL

Executes a stored procedure.

Syntax:

>>--EXEC SQL--.--------------------.--.----------------.->
              +-FOR :host_integer--+  +- :result_hvar -+

 >---CALL stored_procedure_name-.------------.-END-EXEC-><
                                | +-- , --+  |
                                | V       |  |
                                +(parameter)-+

Parameters:

`host_integer`	A host variable that specifies the maximum number of host array elements processed. Must be declared as PIC S9(4) COMP-5 or PIC S9(9) COMP-5.
`result_hvar`	A host variable to receive the procedure result.
`stored_procedure_name`	The name of the stored procedure.
`parameter`	A literal, a DECLARE CURSOR statement¹, or a host variable parameter of the form: [`keyword`=]:`param_hvar` [IN \| INPUT \| INOUT \| OUT \| OUTPUT] where:
	`keyword` ²	The formal parameter name for a keyword parameter. Keyword parameters can be useful as an aid to readability and where the server supports default parameter values and optional parameters.
	`param_hvar`	A host variable.
	IN	An input parameter.
	INPUT	An input parameter (default).
	INOUT	An input/output parameter.
	OUT	An output parameter.
	OUTPUT	An output parameter.
¹ Specify DECLARE CURSOR only with Oracle 8 or later and for stored procedures that return a result set. The use of DECLARE CURSOR unbinds the corresponding parameter. ² HCOSS does not support keyword parameters.

Comments:

Do not use the FOR clause if the CALL is part of a DECLARE CURSOR statement.

For maximum portability, observe the following as general rules:

Avoid literal parameters
Use host variable parameters
Avoid mixing positional parameters and keyword parameters
If your server supports a mixture of positional and keyword parameters, list keyword parameters after positional parameters

Examples:

Call a stored procedure using two positional host variables as input parameters:

   EXEC SQL
      CALL myProc(param1,param2)
   END-EXEC

Call a stored procedure using a keyword host variable as an input parameter:

   EXEC SQL 
      CALL myProc (namedParam=:paramValue) 
   END-EXEC

Call a stored procedure using a result host variable and a keyword host variable as an input parameter:

   EXEC SQL
      :myResult = CALL myFunction(namedParam=:paramValue)
   END-EXEC

Call a stored procedure using two positional host variables, one as an input parameter and one as an output parameter:

   EXEC SQL
      CALL getDept(:empName IN, :deptName OUT)
   END-EXEC

Call a stored procedure using a DECLARE CURSOR statement and a positional host variable as an input parameter (Oracle only):

   EXEC SQL
      DECLARE cities CURSOR FOR CALL locateStores(:userState)
   END-EXEC