RETURN statement

Description

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

Syntax

RETURN [ ( expression ) ]

Examples

Example 1

Returns the product of three numbers:

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

Example 2

Calculates the product of three numbers:

SELECT product (2, 3, 4)

product (2,3,4)
24

Example 3

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 Customers)
  OR customer_id IS NULL THEN
    RETURN
  ELSE
    SELECT ID,sum(
      SalesOrderItems.Quantity )
    FROM Products,
        SalesOrderItems,
        SalesOrders
    WHERE SalesOrders.CustomerID = customer_id
    AND SalesOrders.ID = SalesOrderItems.ID
    AND SalesOrderItems.ProductID = Products.D
    GROUP BY Products.ID
  END IF
END

Usage

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.

None

Standards

Permissions

None

See also