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.

Getting ready

In this section, we will see a few examples where we can use named and default parameters in the SQL stored procedure. We need the following privileges or authorities to create a stored procedure:

• CREATIN or IMPLICIT schema privilege, whichever is applicable

• Privileges needed to execute all the SQL statements used in 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)@

How it works…

In the earlier examples, we had learnt that in addition to providing the default values, DB2 9.7 also provides the flexibility for a developer to change the parameter order in which the procedure can be invoked. With this new capability of DB2 9.7, the application developer can code less error-prone SQL procedures.

• We don't need to specify the parameters in the order of procedure definition.

• We can also define the DEFAULT values for parameters.

• We don't need to specify all parameters in the procedure call statement. In such cases, the default values will be used.

• Applications become easier to read and understand.