>>-RETURN--+------------------------------------------+--;-----><
           | .-,-------------------.                  |
           | V                (1)  |                  |
           '---| Expression |------+--+-------------+-'
                                      '-WITH RESUME-'
 

1. See Expression

In Dynamic Server, for backward compatibility, you can use the RETURN statement inside a CREATE PROCEDURE statement to create an SPL function. By only using RETURN in CREATE FUNCTION statements, however, you can maintain the convention of using CREATE FUNCTION to define routines that return a value, and CREATE PROCEDURE for other routines.

All RETURN statements in the SPL function must be consistent with the RETURNING clause of the CREATE FUNCTION (or CREATE PROCEDURE) statement that defines the function. Any RETURN list of expressions must match in cardinality (and be of data types compatible with) the ordered list of data types in the RETURNING clause of the function definition.

Alternatively, however, the RETURN statement can specify no expressions, even if the RETURNING clause lists one or more data types. In this case, a RETURN statement that specifies no expression is equivalent to returning the expected number of NULL values to the calling context. A RETURN statement without any expressions exits only if the SPL function is declared as not returning any values. Otherwise it returns NULL values.

The following SPL function has two valid RETURN statements:

CREATE FUNCTION two_returns (stockno INT)  RETURNING CHAR (15);
   DEFINE des CHAR(15);
   ON EXCEPTION (-272)     -- if user does not have select privilege
      RETURN;                        -- return no values.
   END EXCEPTION;
   SELECT DISTINCT descript INTO des FROM stock 
      WHERE stocknum = stockno;
   RETURN des;
END FUNCTION

A program that calls the function in the previous example should test whether no values are returned and act accordingly.

If you use the WITH RESUME keyword, after the RETURN statement executes, the next invocation of the SPL function (upon the next FETCH or FOREACH statement) starts from the statement that follows the RETURN statement. Any function that executes a RETURN WITH RESUME statement must be invoked within a FOREACH loop, or else in the FROM clause of a query. If an SPL routine executes a RETURN WITH RESUME statement, a FETCH statement in an ESQL/C application can call the SPL routine.

The following example shows a cursor function that another UDR can call. After the RETURN WITH RESUME statement returns each value to the calling UDR or program, the next line of series executes the next time series is called. If the variable backwards equals zero (0), no value is returned to the calling UDR or program, and execution of series stops:

CREATE FUNCTION series (limit INT, backwards INT) RETURNING INT;
   DEFINE i INT;
   FOR i IN (1 TO limit)
      RETURN i WITH RESUME;
   END FOR
   IF backwards = 0 THEN
      RETURN;
   END IF
   FOR i IN (limit TO 1 STEP -1)
      RETURN i WITH RESUME;
   END FOR
END FUNCTION -- series