RETURN statement

Exits from a function, procedure, or batch unconditionally, optionally providing a return value.

Syntax

RETURN [ expression ]

Remarks

A RETURN statement causes an immediate exit from a block of SQL. If expression is supplied, the value of expression is returned as the value of the function or procedure. Subqueries cannot be used in expression.

If the RETURN appears inside an inner BEGIN block, it is the outer BEGIN block that is terminated.

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.

Privileges

None.

Side effects

None.

Standards

ANSI/ISO SQL Standard
Core Feature.

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.000000

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