ASA Programming Guide
  Embedded SQL Programming
    Using stored procedures

Using simple stored procedures

You can create and call stored procedures in embedded SQL.

You can embed a CREATE PROCEDURE just like any other data definition statement, such as CREATE TABLE. You can also embed a CALL statement to execute a stored procedure. The following code fragment illustrates both creating and executing a stored procedure in embedded SQL:

EXEC SQL CREATE PROCEDURE pettycash( IN amount
   DECIMAL(10,2) )
BEGIN
   UPDATE account
   SET balance = balance - amount
   WHERE name = 'bank';

   UPDATE account
   SET balance = balance + amount
   WHERE name = 'pettycash expense';
END;
EXEC SQL CALL pettycash( 10.72 );

If you wish to pass host variable values to a stored procedure or to retrieve the output variables, you prepare and execute a CALL statement. The following code fragment illustrates the use of host variables. Both the USING and INTO clauses are used on the EXECUTE statement.

EXEC SQL BEGIN DECLARE SECTION;
   double  hv_expense;
   double  hv_balance;
EXEC SQL END DECLARE SECTION;

// code here
EXEC SQL CREATE PROCEDURE pettycash(
         IN expense    DECIMAL(10,2),
         OUT endbalance DECIMAL(10,2) )
   BEGIN
      UPDATE account
      SET balance = balance - expense
      WHERE name = 'bank';

      UPDATE account
      SET balance = balance + expense
      WHERE name = 'pettycash expense';

      SET endbalance = ( SELECT balance FROM account
                         WHERE name = 'bank' );
   END;

EXEC SQL PREPARE S1 FROM 'CALL pettycash( ?, ? )';
EXEC SQL EXECUTE S1 USING :hv_expense INTO :hv_balance;

For more information, see EXECUTE statement [ESQL], and PREPARE statement [ESQL].