Creating Compound Procedures

A compound procedure is a procedure that consists of two or more subprocedures. Compound procedures must contain at least one SELECT subprocedure combined with a SELECT, UPDATE or DELETE subprocedure. By combining one or more subprocedure into a compound procedure, you can perform more than one query level task at the same time, like selecting several records and updating or deleting them. Compound procedures cannot include an INSERT subprocedure.

When you create a compound procedure, the order the subprocedures are listed is the order they will be invoked. The first subprocedure in a compound must be a SELECT procedure. Output parameters from a prior procedure supply the filter parameters for the following procedure.

Before you can create a compound procedure, you must already have created the subprocedures that make up this compound procedure. If you have not done so, read the instructions on creating procedures.

To create a compound procedure:

1. Click the table in the Tables and procedures box for which you want to create the new compound procedure.

2. Click New in the Tables dialog box and select Compound procedure from the list in the Create a new table or procedure dialog box.

3. A new compound procedure appears beneath the current table; enter a name for the new compound procedure in the Name box.

4. Enter a description of the compound procedure in the Description box.

5. Select the compound procedure's type.

   Compound procedures can have a type of either SELECT, UPDATE, or DELETE. A compound procedure cannot include an INSERT procedure. If a compound procedure is built from subprocedures of differing types, the compound procedure's type is considered to be the last subprocedure's type.

6. Click the Insert button next to the Select procedures box and select the first SELECT subprocedure to add to this procedure.

   Note: The first subprocedure in a compound must be a SELECT procedure. The SELECT procedures available in the Select procedures box are all the SELECT procedures in the table. Next to each SELECT procedure is a green dot, a yellow dot, or a red X. If the SELECT procedure is marked with a green dot, it can be used as a valid subprocedure. If it is marked with a yellow dot, the subprocedure doesn't provide any additional parameters that aren't already present. If it is marked with a red X, the inputs for this subprocedure are not available as outputs from a previous SELECT subprocedure.

7. Repeat step 6 for each SELECT subprocedure you want to add to the compound procedure.

8. To change the order that the SELECT subprocedures are invoked, highlight a subprocedure and click the up or down arrow to change its place in the execution order.

9. Optionally, select the UPDATE or DELETE subprocedure to add to the compound procedure by clicking the down arrow next to the Update/Delete procedure list.

The UPDATE or DELETE procedures in the list are those available in the current table. A compound procedure can contain only one UPDATE or DELETE subprocedure, and it is always the last subprocedure in the compound procedure.

 

Compound Procedures that Use PerformTableProcedure

You can also create procedures that use the Host Integrator connectors' PerformTableProcedure rather than the SQL API. In this case, the Available for SQL queries check box at the bottom of the Tables dialogue box should be cleared. Review the information on Executing Procedures Using Connector APIs.