RETURN statement

The RETURN statement is used to return from the routine.

  • For an SQL scalar function, the scalar result of the function is returned. The body of an SQL scalar function must contain at least one RETURN statement and a RETURN statement must be executed when the function is invoked.
  • For an SQL table function, the result table of the function is returned. A RETURN statement must be specified in the body of an SQL table function.
  • For an SQL procedure, the RETURNS statement optionally returns an integer status value.

Syntax

Read syntax diagramSkip visual syntax diagramlabel: RETURN expressionNULLfullselect

Description

label
Specifies the label for the RETURN statement. A label name cannot be the same as the routine name or another label within the same scope. For additional information, see References to SQL labels.
expression
Specifies a value that is returned from the routine.
  • If the routine is a scalar function, the data type of the result must be assignable to the data type that is defined for the function result, using the storage assignment rules as described in Assignment and comparison. The RETURN statement must not contain a period specification.

    The body of any SQL function must not contain a reference to an aggregate function, or a reference to a user-defined function that is sourced on an aggregate function, or an OLAP specification.

    In the body of an inlined SQL function, the RETURN statement must not contain a scalar fullselect. If the expression does not conform to this rule, Db2 attempts to define a compiled function. To determine what type of SQL scalar function is created, refer to the INLINE column of the SYSIBM.SYSROUTINES catalog table. For more information about creating the two types of SQL scalar functions, see CREATE FUNCTION statement (inlined SQL scalar function) and CREATE FUNCTION statement (compiled SQL scalar function).

  • If the routine is a table function, a scalar expression (other than a scalar fullselect) cannot be specified. The data type of the result column of the fullselect must be assignable to the data type that is defined for the function result, using the storage assignment rules as described in Assignment and comparison. The outer fullselect in the RETURN statement of an SQL table function must not contain an offset-clause or fetch-clause, and the RETURN statement must not contain a period specification.
  • If the routine is a procedure, the data type of expression must be INTEGER.
NULL
The null value is returned from the SQL routine.
  • If the routine is a scalar function, the null value is returned.
  • If the routine is a table function, NULL must not be specified.
  • If the routine is a procedure, NULL must not be specified.
fullselect
Specifies the row or rows that are returned from the routine.
  • If the routine is a scalar function, the function is a compiled function and the fullselect must return one column and, at most, one row. The data type of the result column must be assignable to the data type that is defined for the function result, using the storage assignment rules as described in Assignment and comparison.
  • If the routine is a table function, the fullselect can return zero or more rows with one or more columns. The number of columns in the fullselect must match the number of columns in the function result. In addition, the data types of the result table columns of the fullselect must be assignable to the data types of the columns that are defined for the function result, using the storage assignment rules as described in Assignment and comparison.
  • If the routine is a procedure, fullselect must not be specified.

Notes

Considerations for SQL functions: A RETURN statement in an SQL function must specify expression, NULL, or fullselect. For an inlined SQL scalar function, only a single expression can be specified in the RETURN statement, and the expression must not include a scalar fullselect. Only a single RETURN statement can be specified in the routine body of an SQL table function. The execution of an SQL function must end with a RETURN statement.

A data change table reference is not allowed in a RETURN statement in an SQL function.

Considerations for SQL procedures:
  • When a RETURN statement is used within an SQL procedure: If a RETURN statement with a specified return value was used to return from a procedure, the SQLCODE, SQLSTATE, and message length in the SQLCA are initialized to zeros and the message text is set to blanks. An error is not returned to the caller.
  • When a RETURN statement is not used within an SQL procedure or when no value is specified: If a RETURN statement was not used to return from a procedure or if a value is not specified on the RETURN statement, one of the following values is set:
    • If the procedure returns with an SQLCODE that is greater or equal to zero, the specified target for DB2_RETURN_STATUS in a GET DIAGNOSTICS statement will be set to a value of zero.
    • If the procedure returns with an SQLCODE that is less than zero, the specified target for DB2_RETURN_STATUS in a GET DIAGNOSTICS statement will be set to a value of '-1'.
  • When the value is returned from an SQL procedure: When a value is returned from a procedure, the caller may access the value using one of the following methods:
    • The GET DIAGNOSTICS statement to retrieve the RETURN_STATUS when the SQL procedure was called from another SQL procedure.
    • The parameter bound for the return value parameter marker in the escape clause CALL syntax (?=CALL...) in a CLI application.
    • Directly from the SQLCA returned from processing the CALL of an SQL procedure by retrieving the value of sqlerrd[0]. When the SQLCODE is less than zero, the sqlerrd[0] value is not set. The application should assume a return status value of '-1'.

Restrictions:

  • The RETURN statement must not be specified in a trigger.
  • An SQL table function must contain a single RETURN statement.

Examples

Example 1: Use a RETURN statement to return from an SQL procedure with a status value of zero if successful or '-200' if not successful. 
BEGIN
       . . .
           GOTO FAIL;
       . . .
SUCCESS: RETURN 0;
   FAIL: RETURN -200;
END
Example 2: Define a scalar function that returns the tangent of a value using the existing sine and cosine functions: 
 CREATE FUNCTION TAN (X DOUBLE)
       RETURNS DOUBLE
       LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION
       DETERMINISTIC
       RETURN SIN(x)/COS(x)