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

Read syntax diagramSkip visual syntax diagramRETURN expressionNULLWITH,common-table-expressionfullselect

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