iAnywhere.com - RETURN statement

RETURN statement

Description

Use this statement to exit from a function or procedure unconditionally, optionally providing a return value.

Syntax

RETURN [ expression ]

Usage

A RETURN statement causes an immediate exit from a function or procedure. If expression is supplied, the value of expression is returned as the value of the function or procedure.

Statements following a RETURN statement are not executed.

Within a function, the expression should be of the same data type as the function's RETURNS data type.

Within a procedure, RETURN is used for Transact-SQL-compatibility, and is used to return an integer error code.

Permissions

None.

Side effects

None.

Standards and compatibility

SQL/92 Persistent stored module feature.
SQL/99 Persistent Stored Module feature.
Sybase Transact-SQL procedures use the RETURN statement to return an integer error code.

Example

The following function returns the product of three numbers:

CREATE FUNCTION product (
   a numeric,
   b numeric,
   c numeric )
RETURNS numeric
BEGIN
   RETURN ( a * b * c );
END

Calculate the product of three numbers:

SELECT product (2, 3, 4)

product(2, 3, 4)
24

The following procedure uses the RETURN statement to avoid executing a complex query if it is meaningless:

CREATE PROCEDURE customer_products
( in customer_id integer DEFAULT NULL)
RESULT ( id integer, quantity_ordered integer )
BEGIN
   IF customer_id NOT IN (SELECT id FROM customer)
   OR 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