id="returnstmt">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

>>-+--------+-RETURN--+------------------+---------------------><
   '-label:-'         +-expression-------+
                      +-NULL-------------+
                      '-query-expression-'
 

 

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 Labels.

expression

Specifies a value that is returned from the routine:

• If the routine is a function, expression must be specified and the value and the value of expression must conform to the SQL assignment rules as described in Assignments and comparisons. If assigning to a string variable, storage assignment rules apply.

• 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 0 is returned.

NULL

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

query-expression

Specifies a query-expression value that is returned from the routine. The query-expression is a common-table-expression or fullselect. A query-expression is only allowed in a table function.

 

Notes

Returning from a procedure:

• If a RETURN statement with a specified return value is used to return from a procedure then the SQLCODE, SQLSTATE, and message length in the SQLCA or diagnostics area are initialized to zeros, and message text is set to blanks. An error is not returned to the caller.

• If a RETURN statement is not used to return from a procedure or if a value is not specified on the RETURN statement,

  • if the procedure returns with an SQLCODE that is greater than or equal to zero, the specified target for DB2_RETURN_STATUS in a GET DIAGNOSTICS statement will be set to a value of 0

  • if the procedure returns with an SQLCODE that is less than zero, the specified target for DB2_RETURN_STATUS in a GET DIAGNOSTICS statement will be set to a value of -1.

• When a value is returned from a procedure, the caller may access the value using:

  • the GET DIAGNOSTICS statement to retrieve the DB2_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 ODBC or JDBC 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. When the SQLCODE is less than zero, the sqlerrd[0] value is not set and the application should assume a return status value of -1.

RETURN restrictions:

• RETURN is not allowed in SQL triggers.

• Only one RETURN statement is allowed in an SQL table function statement routine-body.

 

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
   failure: RETURN -200
...

END                                      

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)                                   

---

[ Top of Page | Previous Page | Next Page | Contents |
Index ]