Using the DEFAULT values and NAMED arguments in procedures

When we define a stored procedure, it also has IN and OUT parameters associated with it. The stored procedures can be invoked from any host language or by command line. To call a stored procedure, we need to provide the procedure name and parameters. Since we need to process the IN and OUT parameter values, we will have to use host language variables. To make this happen, we can use parameter markers.

A parameter marker acts as a place holder in an SQL statement. Normally, parameter markers are identified by question marks (?). DB2 9.7 also provides support for named parameter markers. It means that we can assign names to the parameter markers and refer to them by using these names. We can also assign DEFAULT values for these parameter markers while creating the procedure.

How to do it…

In this example, we will create a procedure with named/default parameters and will see how we can use named parameters while invoking the procedure.

• Creating a stored procedure with named parameters: We will create a stored procedure with two input parameters, both defined with DEFAULT values. The first input parameter accepts a DATE value and another parameter accepts an offset value. The procedure calculates the month by adding up the date and offset. As we are aware, in DB2 9.7, when defining the stored procedure, the application developer can provide default values so that if there is no input from the user while invoking the procedure, it uses the default set values.

CREATE PROCEDURE namedParmDefault ( OUT out_month SMALLINT, IN in_date DATE DEFAULT '1900-01-01', IN in_offset INT DEFAULT 0) LANGUAGE SQL BEGIN SELECT MONTH(in_date + in_offset DAYS) INTO out_month FROM SYSIBM.SYSDUMMY1; END @ 

• Calling the procedure with named parameters: In the preceding example, we illustrated how to call a normal procedure in any DB2 version. With the default and named parameters, we need not provide all input values in the procedure call statement. We also don't have to use the same order of values as the parameters are defined in the procedure. Consider the following examples where both these cases are illustrated:

CALL NamedParmDefault(?)@ CALL NamedParmDefault(?, in_offset=>100, in_date=>CURRENT DATE)@