To exit from a function or procedure unconditionally, optionally providing a return value. Statements following RETURN are not executed.
RETURN [ ( expression ) ]
Procedures, triggers, and batches
None.
None.
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 for procedures is for use with Transact-SQL-compatible procedures. For information, see "Transact-SQL Procedure Language".
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