RETURN statement

The RETURN statement returns from a routine. For SQL functions, it returns the result of the function. For an SQL procedure, it optionally returns an integer status value. For SQL table functions, it returns a table as the result of the function.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-+--------+--RETURN------------------------------------------->
   '-label:-'           

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

Description

label
Specifies the label for the RETURN statement. The label name cannot be the same as the routine name or another label within the same scope. For more 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 defined for the function result, using the storage assignment rules as described in Assignments and comparisons. An aggregate function, or user-defined function that is sourced on an aggregate function must not be specified for a RETURN statement in an SQL scalar function.
  • If the routine is a table function, a scalar expression (other than a scalar fullselect) cannot be specified.
  • If the routine is a procedure, the data type of expression must be INTEGER. If the expression evaluates to the null value, a value of zero is returned.
NULL
Specifies that the null value is returned from the 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.
WITH common-table-expression
Specifies one or more common table expressions to be used in the fullselect.
fullselect
Specifies the row or rows to be returned for the routine.
  • If the routine is a scalar function, 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 defined for the function result, using the storage assignment rules as described in Assignments and comparisons.
  • 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 defined for the function result, using the storage assignment rules as described in Assignments and comparisons.
  • If the routine is a procedure, fullselect must not be specified.

Notes

Returning from a procedure:

RETURN restrictions:

Example

Example 1: 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
   failure: 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 mytan (x DOUBLE)
   RETURNS DOUBLE
   LANGUAGE SQL
   CONTAINS SQL
   NO EXTERNAL ACTION
   DETERMINISTIC
   RETURN SIN(x)/COS(x)