Parameters in SQL procedures

Parameters can be useful in SQL procedures when implementing logic that is conditional on a particular input or set of input scalar values or when you need to return one or more output scalar values and you do not want to return a result set.

SQL procedures support parameters for the passing of SQL values into and out of procedures.

It is good to understand the features of and limitations of parameters in SQL procedures when designing or creating SQL procedures.
  • Db2® supports the optional use of a large number of input, output, and input-output parameters in SQL procedures. The keywords IN, OUT, and INOUT in the routine signature portion of CREATE PROCEDURE statements indicate the mode or intended use of the parameter. IN and OUT parameters are passed by value, and INOUT parameters are passed by reference.
  • When multiple parameters are specified for a procedure they must each have a unique name.
  • If a variable is to be declared within the procedure with the same name as a parameter, the variable must be declared within a labeled atomic block nested within the procedure. Otherwise Db2 will detect what would otherwise be an ambiguous name reference.
  • Parameters to SQL procedures cannot be named either of SQLSTATE or SQLCODE regardless of the data type for the parameter.

Refer to the CREATE PROCEDURE (SQL) statement for complete details about parameter references in SQL procedures.

The following SQL procedure named myparams illustrates the use of IN, INOUT, and OUT parameter modes. Let us say that SQL procedure is defined in a CLP file named myfile.db2 and that we are using the command line. 
 
  CREATE PROCEDURE myparams (IN p1 INT, INOUT p2 INT, OUT p3 INT)
  LANGUAGE SQL
  BEGIN
    SET p2 = p1 + 1;
    SET p3 = 2 * p2;
  END@