CREATE PROCEDURE

This SQL statement can be executed in the following ways:	Through the CA Datacom Datadictionary Interactive SQL Service Facility (interactive)	In an application program prepared using a CA Datacom/DB SQL Preprocessor (embedded)	By using CA Dataquery (SQL & Batch Modes)
CREATE PROCEDURE	YES/NO	YES	YES/NO

Note: YES indicates a valid execution method for this statement.

YES/NO means YES for External Procedures but NO for SQL Procedures (see following explanations of External Procedures and SQL Procedures).

External Procedure definitions and SQL Procedures are created with this statement. External Procedures are written in a host programming language (COBOL, PL/I, C, or ASSEMBLER) with SQL statements imbedded in their programs. For an overview and examples of External Procedures and triggers, see Procedures and Triggers. SQL Procedures are written in SQL and have all of their execution-time logic included in an SQL CREATE PROCEDURE statement.

There are three ways to execute procedures:

CALL statements (see CALL/EXECUTE PROCEDURE)
EXECUTE PROCEDURE statements (see CALL/EXECUTE PROCEDURE)
Triggers (see CREATE TRIGGER/RULE)

We recommend using PLNCLOSE=T for procedures instead of PLNCLOSE=R. For External Procedures, the PLNCLOSE= specification is determined by the Preprocessor options used for the procedure body compilation. For SQL Procedures, the PLNCLOSE= specification is determined by the options used by the plan in which the CREATE PROCEDURE statement is executed. When using DBSQLPR, specify PLNCLOSE=T or allow PLNCLOSE to default to that value.

When you code procedures that perform INSERTs or UPDATEs, keep in mind that if they are called or triggered (directly or indirectly) from an application that reads the same table on which that procedure is operating, the procedure affects the results of the calling or triggering application and, in extreme cases, can cause loops to occur. For example, if an application is performing a searched UPDATE of a table, and the UPDATE triggers a procedure that does UPDATEs itself, the procedure could cause an endless loop by moving previously updated rows back into the traversal path of the controlling searched update. Because CA Datacom/DB cannot prevent such a situation from occurring, it is important that you make every effort to ensure your coding of a procedure does not cause this to occur.

Triggers whose procedures perform INSERTs, UPDATEs, or DELETEs have the potential to trigger themselves recursively, triggering themselves immediately, or passing through other triggers first. However, be aware that the maximum recursion depth specified in the PROCEDURE Multi-User startup option cannot be exceeded.