ASA SQL User's Guide
  Using Procedures, Triggers, and Batches
    The structure of procedures and triggers

Declaring parameters for procedures

Procedure parameters appear as a list in the CREATE PROCEDURE statement. Parameter names must conform to the rules for other database identifiers such as column names. They must have valid data types (see SQL Data Types), and must be prefixed with one of the keywords IN, OUT or INOUT. These keywords have the following meanings:

• IN    The argument is an expression that provides a value to the procedure.

• OUT    The argument is a variable that could be given a value by the procedure.

• INOUT    The argument is a variable that provides a value to the procedure, and could be given a new value by the procedure.

You can assign default values to procedure parameters in the CREATE PROCEDURE statement. The default value must be a constant, which may be NULL. For example, the following procedure uses the NULL default for an IN parameter to avoid executing a query that would have no meaning:

CREATE PROCEDURE
CustomerProducts( IN customer_id
                  INTEGER DEFAULT NULL )
RESULT ( product_id INTEGER,
         quantity_ordered INTEGER )
BEGIN
    IF customer_id IS NULL THEN
        RETURN;
    ELSE
        SELECT    product.id,
                 sum( sales_order_items.quantity )
        FROM   product,
                 sales_order_items,
                 sales_order
        WHERE sales_order.cust_id = customer_id
        AND sales_order.id = sales_order_items.id
        AND sales_order_items.prod_id = product.id
        GROUP BY product.id;
    END IF;
END

The following statement assigns the DEFAULT NULL, and the procedure RETURNs instead of executing the query.

CALL customerproducts();