Executing the SQL Statement CALL

Use the CALL statement to execute each series of SQL statements in your application. For example, to call the stored procedure described in How an Application Works With a Stored Procedure, your application might use this statement:

EXEC SQL
  CALL STPROC1
    (:V1, :V2)
END-EXEC

If you use host variables in the CALL statement, you must declare them before using them.

• The example above is based on the assumption that none of the input parameters can have null values. To allow null values, code a statement like this:

  EXEC SQL
    CALL STPROC1 (:V1 :IV1, :V2 :IV2)
  END-EXEC

  where :IV1 and :IV2 are indicator variables for the parameters.

• To pass integer or character string constants or the null value to the stored procedure, code a statement like this:

  EXEC SQL
    CALL STPROC1 (2, NULL)
  END-EXEC

• To use a host variable for the name of the stored procedure, code a statement like this:

  EXEC SQL
    CALL :PROCNAME (:V1, :V2)
  END-EXEC

  Assume that the stored procedure name is STPROC1. The host variable PROCNAME is a character variable of length 254 or less that contains the value STPROC1. You should use this technique if you do not know in advance the name of the stored procedure, but you do know the parameter list convention.

• To pass your parameters in a single structure, rather than as separate host variables, code a statement like this:

  EXEC SQL
    CALL STPROC1 USING DESCRIPTOR :ADDVALS
  END-EXEC

  where ADDVALS is the name of an SQLDA.

• To use a host variable name for the stored procedure with an SQLDA, code a statement like this:

  EXEC SQL
    CALL :PROCNAME USING DESCRIPTOR :ADDVALS
  END-EXEC

  This form gives you extra flexibility because you can use the same CALL statement to invoke different stored procedures with different parameter lists.

  Your client program must assign a stored procedure name to the host variable PROCNAME and load the SQLDA ADDVALS with the parameter information before making the SQL CALL statement.

Each of the above CALL statement examples uses an SQLDA. If you do not explicitly provide an SQLDA, the precompiler generates the SQLDA based on the variables in the parameter list.

You can execute the CALL statement only from an application program. You cannot use the CALL statement dynamically.