Getting Started › Procedures and Triggers › Example: Calling a Procedure › Using Embedded SQL

Using Embedded SQL

When a CALL/EXECUTE PROCEDURE statement is embedded in a program, host variables can be used in the parameter list. Assuming that the procedure in the procedure creation previously shown example included the output parameter "orders-canceled" as the fourth parameter, the CALL statement embedded in the application program might look something like this:

 CALL item_order_killer(:item_id, :vendor_id, 5, :NUM-ORDERS-CANCELED)

The NUM-ORDERS-CANCELED would have to be declared as an integer variable in the program. Note that the first two parameters have also been changed to host variables. This is because outside the CREATE TRIGGER statement, CALL parameters cannot refer to columns in a table. They can, however, be host variables containing column values. Since these are input-only parameters (IN, as opposed to INOUT or OUT), literals and expressions can also be passed. OUT and INOUT parameters must be host variables, since data is returned to the caller.