RETURN statement

The RETURN statement is used to return from a routine. For SQL functions or methods, it returns the result of the function or method. For an SQL procedure, it optionally returns an integer status value.

Invocation

This statement can be embedded in an SQL function, SQL method, or SQL procedure. It is not an executable statement and cannot be dynamically prepared.

Authorization

No privileges are required to invoke the RETURN statement. However, the authorization ID of the statement must hold the necessary privileges to invoke any expression or fullselect that is embedded in the RETURN statement.

Syntax

>>-RETURN--+---------------------------------------------------+-><
           +-expression----------------------------------------+   
           +-NULL----------------------------------------------+   
           '-+-----------------------------------+--fullselect-'   
             |       .-,-----------------------. |                 
             |       V                         | |                 
             '-WITH----common-table-expression-+-'                 

Description

expression

Specifies a value that is returned from the routine:

• If the routine is a function or method, one of expression, NULL, or fullselect must be specified (SQLSTATE 42631) and the data type of the result must be assignable to the RETURNS type of the routine (SQLSTATE 42866).
• If the routine is a table function, a scalar expression (other than a scalar fullselect) cannot be specified (SQLSTATE 428F1).
• If the routine is a procedure, the data type of expression must be INTEGER (SQLSTATE 428F2). A procedure cannot return NULL or a fullselect.

NULL

Specifies that the function or method returns a null value of the data type defined in the RETURNS clause. NULL cannot be specified for a RETURN from a procedure.

WITH common-table-expression

Defines a common table expression for use with the fullselect that follows.

fullselect

Specifies the row or rows to be returned for the function. The number of columns in the fullselect must match the number of columns in the function result (SQLSTATE 42811). In addition, the static column types of the fullselect must be assignable to the declared column types of the function result, using the rules for assignment to columns (SQLSTATE 42866).

The fullselect cannot be specified for a RETURN from a procedure.

If the routine is a scalar function or method, then the fullselect must return one column (SQLSTATE 42823) and, at most, one row (SQLSTATE 21000).

If the routine is a row function, it must return, at most, one row (SQLSTATE 21505). However, one or more columns can be returned.

If the routine is a table function, it can return zero or more rows with one or more columns.

Rules

• The execution of an SQL function or method must end with a RETURN statement (SQLSTATE 42632).
• In an SQL table or row function using a compound SQL (inlined) statement, the only RETURN statement allowed is the one at the end of the compound statement. (SQLSTATE 429BD).

Notes

• When a value is returned from a procedure, the caller can access the value:
  • using the GET DIAGNOSTICS statement to retrieve the DB2_RETURN_STATUS when the SQL procedure was called from another SQL procedure
  • using the parameter bound for the return value parameter marker in the escape clause CALL syntax (?=CALL...) in a CLI application
  • directly from the sqlerrd[0] field of the SQLCA, after processing the CALL of an SQL procedure. This field is only valid if the SQLCODE is zero or positive (assume a value of -1 otherwise).

Examples

   BEGIN
   ...
     GOTO FAIL;
   ...
     SUCCESS: RETURN 0;
     FAIL: RETURN -200;
   END