Routine invocation using named parameters
DB2 for LUW supports functions with up to 90 parameters and for procedures the limit it 32000.
While I hope few if any users actually reach these limits, routines with a dozen or more parameters are not uncommon.
Let's take a look at a procedure invocation:
Chances are the procedure's definition looked something like:
CREATE OR REPLACE PROCEDURE tota
But can we be sure? What will happen happen if the application developer mixed up the order of the arguments?
Perhaps total_comp is the first parameter rather than the last?
This thought is troublesome enough. but now imagine using constants or parameter markers:
VARIABLE result DECIMAL(9,2);
This is completely unreadable!
Sometimes developers do the following:
This clearly improves readability, but there is no guarantee that there is no mix-up.
Named parameter invocation
DB2 9.7 introduced named parameter invocation in procedure and shortly after in routines.
Named parameter invocation is not a new concept. Personally I remember using this capability is LISP during my university years.
It has also been introduced into SQL by other vendors.
Normally, when invoking a routine the first argument is assigned to the first parameter, the second argument to the second parameter and so on.
This is called the positional syntax for routine invocation.
In named syntax however you associate the arguments to the routine to the parameters by name.
Here is an example:
CALL total_compensation( total_comp => :result,
Note that this syntax is not only more readable, it also allows me to mix up the order.
There is no need to remember which parameter goes where.
There is however a need to know the names of para
The rule for named parameter invocation are quite simple:
First you have to know the exact parameter names and type them in. This is where IDE's could provide some real help
But the bigger downside is that you now have a dependency on the parameter name used that is not recorded anywhere.
So if someone changes the names or a procedure parameters the invoking routine will fail to automatically recompile
If you have a good coding standards around CamelCasing, using underbars and so on, then neither reason should be big problem.
All in all I think named parameter invocation is a great feature addition to DB2 which aids in application development and maintenance.