RETURN statement

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

Syntax


>>-RETURN--+------------+--------------------------------------><
           +-expression-+   
           '-NULL-------'

Description

expression

Specifies a value that is returned from the routine.

If the routine is a function, expression must be specified and the value of expression must conform to the SQL assignment rules as described in Assignment and comparison. If the value is being assigned to a string variable, storage assignment rules apply.
If the routine is a procedure, the data type of expression must be INTEGER. If expression evaluates to the null value, a value of 0 is returned.

The expression cannot include a column name or a host variable. See Expressions for information on expressions. The expression cannot contain a scalar fullselect.

NULL

The null value is returned from the SQL function. NULL is not allowed in SQL procedures.

Notes

When a RETURN statement is not used within an SQL procedure or when no value is specified: If a RETURN statement was not used to return from a procedure or if a value is not specified on the RETURN statement, one of the following values is set:

If the procedure returns with an SQLCODE that is greater or equal to zero, the return status is set to a value of '0'.
If the procedure returns with an SQLCODE that is less than zero, the return status is set to a value of '-1'.

When a RETURN statement is used within an SQL procedure: If a RETURN statement with a specified return value was used to return from a procedure, the SQLCODE, SQLSTATE, and message length in the SQLCA are initialized to zeros and the message text is set to blanks. An error is not returned to the caller.

When the value is returned: When a value is returned from a procedure, the caller may access the value using one of the following methods:

The GET DIAGNOSTICS statement to retrieve the RETURN_STATUS when the SQL procedure was called from another SQL procedure.
The parameter bound for the return value parameter marker in the escape clause CALL syntax (?=CALL...) in a CLI application.
Directly from the SQLCA returned from processing the CALL of an SQL procedure by retrieving the value of sqlerrd[0]. When the SQLCODE is less than zero, the sqlerrd[0] value is not set. The application should assume a return status value of '-1'.

Examples

Use a RETURN statement to return from an SQL procedure with a status value of zero if successful or '-200' if not successful.

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