Using Executable Procedures

Go Up to Working with Stored Procedures

An executable procedure is called directly by an application, and often performs a task common to applications using the same database. Executable procedures can receive input parameters from the calling program, and can optionally return a single row to the calling program.

Input parameters pass to an executable procedure in a comma-delimited list following the procedure name.

Note:
Executable procedures cannot return multiple rows.

Executing a Procedure

To execute a procedure in an application, use the following syntax:

EXEC SQL
EXECUTE PROCEDURE name [:param [[INDICATOR]:indicator]]
[, :param [[INDICATOR]:indicator] ...]
[RETURNING_VALUES :param [[INDICATOR]:indicator]
[, :param [[INDICATOR]:indicator]...]];

When an executable procedure uses input parameters, the parameters can be literal values (such as 7 or “Fred”), or host variables. If a procedure returns output parameters, host variables must be supplied in the RETURNING_VALUES clause to hold the values returned.

For example, the following statement demonstrates how the executable procedure, DEPT_BUDGET, is called with literal parameters:

EXEC SQL
EXECUTE PROCEDURE DEPT_BUDGET 100 RETURNING_VALUES :sumb;

The following statement also calls the same procedure using a host variable instead of a literal as the input parameter:

EXEC SQL
EXECUTE PROCEDURE DEPT_BUDGET :rdno RETURNING_VALUES :sumb;

Indicator Variables

Both input parameters and return values can have associated indicator variables for tracking NULL values. You must use indicator variables to indicate unknown or NULL values of return parameters. The INDICATOR keyword is optional. An indicator variable that is less than zero indicates that the parameter is unknown or NULL. An indicator variable that is 0 indicates that the associated parameter contains a non-NULL value. For more information about indicator variables, see Working with Data.

Executing a Procedure in a DSQL Application

To execute a stored procedure in a dynamic SQL (DSQL) application, follow these steps:

1. Use a PREPARE statement to parse and prepare the procedure call for execution, using the following syntax:

   EXEC SQL
   PREPARE sql_statement_name FROM :var | '<statement>';
   

2. Set up an input XSQLDA using the following syntax:

   EXEC SQL
   DESCRIBE INPUT sql_statement_name INTO SQL DESCRIPTOR input_xsqlda;
   

3. Use DESCRIBE OUTPUT to set up an output XSQLDA using the following syntax:

   EXEC SQL
   DESCRIBE OUTPUT sql_statement_name INTO SQL DESCRIPTOR
   output_xsqlda;
   

   Setting up an output XSQLDA is only necessary for procedures that return values.
4. Execute the statement using the following syntax:

   EXEC SQL
   EXECUTE statement USING SQL DESCRIPTOR input_xsqlda
   INTO DESCRIPTOR output_xsqlda;
   

Input parameters to stored procedures can be passed as run-time values by substituting a question mark (?) for each value. For example, the following DSQL statements prepare and execute the ADD_EMP_PROJ procedure:

. . .
strcpy(uquery, "EXECUTE PROCEDURE ADD_EMP_PROJ ?, ?");
. . .
EXEC SQL
PREPARE QUERY FROM :uquery;
EXEC SQL
DESCRIBE INPUT QUERY INTO SQL DESCRIPTOR input_xsqlda;
EXEC SQL
DESCRIBE OUTPUT QUERY INTO SQL DESCRIPTOR output_xsqlda;
EXEC SQL
EXECUTE QUERY USING SQL DESCRIPTOR input_xsqlda INTO SQL DESCRIPTOR
output_xsqlda;
. . .

Advance To:

• Embedded SQL Guide