Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Using Procedures, Triggers, and Batches » The structure of procedures and triggers

The structure of procedures and triggers Next Page

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 can be prefixed with one of the keywords IN, OUT or INOUT. By default, parameters are INOUT parameters. These keywords have the following meanings:

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 )
         quantity_ordered INTEGER )
   IF customer_ID IS NULL THEN
      SELECT    Products.ID,
               sum( SalesOrderItems.Quantity )
        FROM   Products,
      WHERE SalesOrders.CustomerID = customer_ID
      AND SalesOrders.ID = SalesOrderItems.ID
      AND SalesOrderItems.ProductID = Products.ID
      GROUP BY Products.ID;
   END IF;

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

CALL CustomerProducts();