Stored procedure parameters

You can pass information between a stored procedure and the calling application program by using parameters. Applications pass the required parameters in the SQL CALL statement. Optionally, the application can also include an indicator variable with each parameter to allow for null values or to pass large output parameter values.

You define the stored procedure parameters as part of the stored procedure definition in the CREATE PROCEDURE statement. The stored procedure parameters can be one of the following types:

IN
Input-only parameters, which provide values to the stored procedure.
OUT
Output-only parameters, which return values from the stored procedure to the calling program.
INOUT
Input and output parameters, which provide values to and return values from the stored procedure.

If a stored procedure fails to set one or more of the OUT or INOUT parameters, Db2 does not return an error. Instead, Db2 returns the output parameters to the calling program, with the values that were established on entry to the stored procedure.

Within a procedure body, the following rules apply to IN, OUT, and INOUT parameters:
  • You can use a parameter that you define as IN on the left side or right side of an assignment statement. However, if you assign a value to an IN parameter, you cannot pass the new value back to the caller. The IN parameter has the same value before and after the SQL procedure is called.
  • You can use a parameter that you define as OUT on the left side or right side of an assignment statement. The last value that you assign to the parameter is the value that is returned to the caller. The starting value of an OUT parameter is NULL.
  • You can use a parameter that you define as INOUT on the left side or right side of an assignment statement. The caller determines the first value of the INOUT parameter, and the last value that you assign to the parameter is the value that is returned to the caller.
Restrictions:
  • You cannot pass file reference variables as stored procedure parameters.
  • You cannot pass parameters with the type XML to stored procedures. You can specify tables or views that contain XML columns as table locator parameters. However, you cannot reference the XML columns in the body of the stored procedure.