ASA SQL Reference
  SQL Statements

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.

See also 

CREATE FUNCTION statement

CREATE PROCEDURE statement

BEGIN statement

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)

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