Output Parameters

Go Up to Using Variables

Output parameters are used to return values from a procedure to the calling application. Declare them in a comma-delimited list in parentheses following the RETURNS keyword in the procedure header. Once declared, they can be used in the procedure body anywhere an expression can appear. For example, the following procedure header declares five output parameters, HEAD_DEPT, DEPARTMENT, MNGR_NAME, TITLE, and EMP_CNT:

CREATE PROCEDURE ORG_CHART
RETURNS (HEAD_DEPT CHAR(25), DEPARTMENT CHAR(25),
MNGR_NAME CHAR(20), TITLE CHAR(5), EMP_CNT INTEGER)

If you declare output parameters in the procedure header, the procedure must assign them values to return to the calling application. Values can be derived from any valid expression in the procedure.

For more information on declaring output parameters in stored procedures, see Declaring Output Parameters.

A procedure returns output parameter values to the calling application with a SUSPEND statement. For more information about SUSPEND, see Using SUSPEND, EXIT, and END With Procedures.

In a SELECT statement that retrieves values from a procedure, the column names must match the names and data types of the procedure’s output parameters. In an EXECUTE PROCEDURE statement, the output parameters need not match the names of the procedure’s output parameters, but the data types must match.