Executing the SQL Statement CALL

Example 1

To call the stored procedure described in How an Application Works with a Stored Procedure, your application might use this statement:

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

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

Example 2

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 GETEMPSVR (:V1 :IV1, :V2 :IV2)
END-EXEC

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

Example 3

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

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

Example 4

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

Example 5

Assume that the stored procedure name is GETEMPSVR. The host variable PROCNAME is a character variable of length 254 or less that contains the value GETEMPSVR. 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 GETEMPSVR USING DESCRIPTOR :ADDVALS
END-EXEC

where ADDVALS is the name of an SQLDA.

Example 6

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.