SQL procedures support parameters for the passing of SQL values
into and out of 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.
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@
DB2 Version 9.7 for Linux, UNIX, and Windows