Stored Procedure Parameter Defaults And Named Arguments In DB2 For i

February 20, 2013 Michael Sansoterra

Starting with IBM i 7.1 Technology Refresh 5, DB2 for i stored procedure parameters support a default value, and the CALL statement supports named arguments. (DB2 for i Group PTF Level 18 or higher provides this new functionality). These simple and related enhancements can have surprising benefits for code comprehension and code maintenance.

Let’s look at creating a procedure with one or more parameters having a default value:

CREATE PROCEDURE DEV.CREATE_ORDER (
IN @CUSTOMER_ID INT,
IN @ORDER_DATE DATE DEFAULT CURRENT_DATE,
IN @ORDER_ID INTEGER DEFAULT (NEXT VALUE for DATALIB.ORDER_ID_SEQUENCE) ,
IN @ORDER_SOURCE VARCHAR(10) DEFAULT 'WEB') 
LANGUAGE SQL
BEGIN
…
END

In this example, three of the four parameters have assigned defaults. If values are not supplied or if the default values are specifically requested, the value or expression specified with the DEFAULT keyword will be passed to the procedure when executed. The first parameter with a default (@ORDER_DATE) is defaulted to the CURRENT_DATE special register.

The second defaulted parameter (@ORDER_ID) will use the next available number in a sequence as its default value. This is nice because if there are 10 procedures calling this procedure to create an order, none of the calling procedures need to contain the code to get the next order ID in the sequence (unless there is a special reason). Instead, they can just let the parameter default take care of it. That simplifies coding.

The last parameter (@ORDER_SOURCE) is defaulted to the literal value ‘WEB’. Although not shown here, a scalar subquery can also be specified as a default value–that’s pretty powerful stuff.

Now that defaults are defined for some of the parameters, there are new options for calling the procedure. Of course, there is the traditional call, supplying all parameters:

CALL DEV.CREATE_ORDER (1,'2013-03-12',100,'ERP');

Next, it is permissible to have a CALL statement that just passes the first (and only required) parameter while the omitted parameters are defaulted:

CALL DEV.CREATE_ORDER (1);

Please note that this example only works because the remaining parameters have a default. This syntax would not work if, for instance, parameter three did not have a default. If a parameter does not have a default, its value must be supplied in the CALL statement.

Another option is to use the DEFAULT keyword in place of a parameter value:

CALL DEV.CREATE_ORDER (1,default,default,default);

Further, parameter values and defaults can be supplied in any combination:

CALL DEV.CREATE_ORDER (1,default,default,'DATA ENTRY');

Finally, note that NULL and DEFAULT are not equivalent:

CALL DEV.CREATE_ORDER (1,NULL,default,'DATA ENTRY');

In this call, the @ORDER_ID parameter would receive a NULL value, not the next sequence number.

Defaults can be defined by a constant, special register, global variable, or an expression. The expression has limitations in that it can’t refer to aggregates or, in my testing, other parameter names. Also, a default cannot be assigned to an OUT parameter.

Now let’s look at the enhancement to the CALL statement for named arguments. Sometimes stored procedures can have an unwieldy parameter list. Let’s say a stored procedure accepts a potentially large number of parameters. In this case the parameters provide filtering criteria when returning results to a customer service order search facility. Take this “Order Search” parameter signature for example:

CREATE OR REPLACE PROCEDURE ORDER_SEARCH (
IN ORDER_NUMBER INT DEFAULT NULL,
IN START_DATE DATE DEFAULT NULL,
IN END_DATE DATE DEFAULT NULL,
IN CUSTOMER_NAME VARCHAR(50) DEFAULT NULL,
IN ORDER_TYPE SMALLINT DEFAULT NULL,
IN ORDER_CLASS SMALLINT DEFAULT NULL,
IN ORDER_VALUE DEC(19,4) DEFAULT NULL,
IN ITEM_NUMBER CHAR(15) DEFAULT NULL,
IN ITEM_NUMBER_2 CHAR(15) DEFAULT NULL,
IN ITEM_NUMBER_3 CHAR(15) DEFAULT NULL,
IN CITY VARCHAR(50) DEFAULT NULL,
IN STATE_CODE CHAR(2) DEFAULT NULL,
IN POSTAL_CODE VARCHAR(10) DEFAULT NULL
)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
 /* Dynamic SQL Code Goes Here */
END

If we want to search for orders with an item number “BIKE_26IN”, the call would normally look like this:

CALL ORDER_SEARCH (NULL,NULL,NULL,NULL,NULL,
NULL,NULL,'BIKE_26IN',NULL,NULL,NULL,NULL,NULL)

Yuk!

However, because this procedure has default values specified, and because the CALL statement supports named parameters, we can reduce the CALL statement to this:

CALL ORDER_SEARCH (ITEM_NUMBER=>'BIKE_26IN')

The “=>” syntax is used to indicate a parameter name followed by the “argument” or value.

This second CALL example illustrates clearly which parameter is being filled in, and thus provides better documentation than the first call. Another benefit to using named parameters is that the procedure’s signature can change without having to review all of the CALL statements. If you’ve ever had a developer insert a new parameter at the beginning of the parameter list and cause other CALLs to the procedure to break, you know why this new method is helpful!

As you might expect, you can specify multiple named arguments:

CALL ORDER_SEARCH (ITEM_NUMBER=>'BIKE_26IN', ORDER_TYPE=>2)

The order of the parameter names doesn’t matter. If a default is not specified on a procedure parameter, then a value must be supplied.

Going back to the initial example, two named arguments can be passed by name like this:

CALL DEV.CREATE_ORDER (&@Customer_Id=>1,&@Data_Source=>'DATA ENTRY')

Parameter defaults and named arguments operate for both SQL and external (high-level language) stored procedures. Hopefully, we will see this default parameter functionality included with user-defined functions soon. In summary, these new features enhance code maintainability and readability. These features also exist in other versions of DB2 and can now be included when writing portable code within the DB2 family.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

New in DB2 for i 7.1: Use Global Variables to Track Environment Settings

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot