Defining and Using Procedures › Defining a Procedure

Defining a Procedure

How to define and deploy SQL-invoked procedures depends on the language of the procedure.

• For SQL procedures which are specified with LANGUAGE SQL, you define the procedure using the CREATE PROCEDURE statement with the procedure-statement clause. After the successful execution and commit of the CREATE PROCEDURE statement, the procedure can be called.
• For external procedures, not specified with LANGUAGE SQL, you must use the following steps:
  1. Define the procedure using the CREATE PROCEDURE statement.
  2. Write the procedure in COBOL, PL/I, Assembler, or CA ADS following the guidelines outlined below. You can also use an existing program as a procedure.
  3. Define the program to a CA IDMS system, if necessary
  4. Invoke the procedure with an SQL CALL statement or from within a query-specification or a SELECT statement.

In the following example, the procedure GET_BONUS is named and associated with schema EMP. The name of the program to be called to service a CALL request of the procedure, CALCSAL, is specified in the EXTERNAL NAME parameter. The PROTOCOL IDMS specifies that the procedure is defined and called using the IDMS protocol. The parameters that pass to and from the procedure are listed. Each parameter definition consists of a name and a data type.

    CREATE PROCEDURE EMP.GET_BONUS
   ( EMP_ID            UNSIGNED NUMERIC (4),
     START_DATE        DATE,
     SALARY              UNSIGNED NUMERIC (9))
   EXTERNAL NAME CALCSAL
   PROTOCOL IDMS;

More Information

• For more information about syntax and parameters used in defining procedures, see CREATE PROCEDURE.
• For more information and a more detailed example of using CREATE PROCEDURE, see Sample COBOL Procedure, and Sample CA ADS Procedure.
• For more information about procedures returning dynamic result sets, see CALL.