Sybase 12.4.2 User Manual

The structure of procedures

 

Procedure parameters, or arguments, are specified 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 be a valid data types 
(see “SQL Data Types” in Adaptive Server IQ Reference Manual), and must be 
prefixed with one of the keywords IN, OUT or INOUT. These keywords have 
the following meanings:

The argument is an expression that provides a value to the 

procedure.

The argument is a variable that could be given a value by the 

procedure.

The argument is a variable that provides a value to the 

procedure, and could be given a new value by the procedure.

Default values can be assigned 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 )

FROMproduct,

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 causes the DEFAULT NULL to be assigned, and the 
procedure returns instead of executing the query.

CALL CustomerProducts();