User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements

Function

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

Syntax

     RETURN [ ( expression ) ]

Usage

Procedures, triggers, and batches

Permissions

None.

Side effects

None.

See also

• CREATE FUNCTION statement
• CREATE PROCEDURE statement
• Compound statements

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 for procedures is for use with Transact-SQL-compatible procedures. For information, see "Transact-SQL Procedure Language".

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