Reference Manual
   CHAPTER 9. SQL Statements     

RETURN statement

Function 

To exit from a function or procedure unconditionally, optionally providing a return value. Statements following RETURN are not executed.

Syntax 

RETURN [ ( expression ) ]

Permissions 

None.

Side effects 

None.

See also 

CREATE FUNCTION statement

CREATE PROCEDURE statement

BEGIN... END statement

Description 

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

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

RETURN is used in procedures for Transact-SQL-compatibility, and is used to return an integer error code.

Standards and compatibility 

• SQL/92     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