SQL Reference

RETURN Statement

The RETURN statement is used to return from the 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.

Syntax

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

Description

expression

Specifies a value that is returned from the routine:

• If the routine is a function or method, expression must be specified (SQLSTATE 42630) and the data type of expression must be assignable to the RETURNS type of the routine (SQLSTATE 42866).
• If the routine is a procedure, the data type of expression must be INTEGER (SQLSTATE 428E2).

Notes

• When a value is returned from a procedure, the caller may access the value using:
  • 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 not less than zero (assume a value of -1 when SQLCODE is less than zero).

Examples

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

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