Getting Started › Procedures and Triggers › Examples: Creating a Procedure › Defining the Procedure to SQL

Defining the Procedure to SQL

You can define the procedure to SQL after you have precompiled the program code. This particular procedure would be defined as follows:

  CREATE PROCEDURE item_order_killer
   (IN canceled_item_id      INTEGER,
    IN distributor-id        INTEGER,
    IN maximum_cancellations INTEGER)
   MODIFIES SQL DATA
   LANGUAGE C (or COBOL or PLI or ASSEMBLER)
   PARAMETER STYLE DATACOM SQL
   EXTERNAL NAME itemkill

The procedure name used previously differs from the external name only to illustrate that the external name equates to a load-module name, and the SQL name is an SQL-identifier. For simplicity, we recommend using the same name in both places. Note that procedure parameters may be any valid SQL data type, but the example uses INTEGER to avoid distracting you from the relevant points.

Note also that while OUT and INOUT parameters are supported, they are not used here because this procedure is used with a trigger. Procedures that use output parameters may not be called from a trigger. If there were no need to call the procedure from a trigger and the caller was interested in knowing how many orders had to be canceled, the procedure definition might look something like this:

  CREATE PROCEDURE item_order_killer
   ( IN    canceled_item_id      INTEGER,
     IN    distributor-id        INTEGER,
     IN    maximum_cancellations INTEGER,
     OUT orders_canceled         INTEGER)
   LANGUAGE C (or COBOL or PLI or ASSEMBLER)
   PARAMETER STYLE DATACOM SQL
   MODIFIES SQL DATA
   EXTERNAL NAME itemkill

Note that the CALL PROCEDURE statement must now supply a host variable to receive the "orders_canceled" output parameter from the procedure.