Using Executable Procedures
Go Up to Working with Stored Procedures
Contents
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.
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:
- 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>';
- Set up an input
XSQLDA
using the following syntax:EXEC SQL DESCRIBE INPUT sql_statement_name INTO SQL DESCRIPTOR input_xsqlda;
- Use
DESCRIBE OUTPUT
to set up an outputXSQLDA
using the following syntax:EXEC SQL DESCRIBE OUTPUT sql_statement_name INTO SQL DESCRIPTOR output_xsqlda;
Setting up an outputXSQLDA
is only necessary for procedures that return values. - 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; . . .