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
Use a RETURN statement to return
from an SQL procedure with a status value of zero if successful, and
-200 if not.
BEGIN
...
GOTO FAIL;
...
SUCCESS: RETURN 0;
FAIL: RETURN -200;
END