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
other than a compiled table function, 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 an inlined table function,
a scalar expression (other than a scalar fullselect) cannot be specified
(SQLSTATE 428F1). If the routine is a compiled table function, an
expression cannot be specified.
- 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 table function, row function, or 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 or a compiled table function.
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 an inlined table function,
it can return zero or more rows with one or more columns. If the fullselect has zero result rows, no
row is returned to the result table by the RETURN statement.
Rules
- The execution of an SQL function or method must end with a RETURN
statement (SQLSTATE 42632).
- In
an SQL table function using a compound SQL (compiled) statement, an expression,
NULL, or fullselectcannot be specified.
Rows are returned from the function using the PIPE statement and the
RETURN statement is required as the last statement to execute when
the function exits (SQLSTATE 2F005).
- 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).
Example
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