Posted by: Serge Rielau
Motivation
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:
CALL total_compensation(salary, percent_bonus, stocks, total_comp);
Chances are the procedure's definition looked something like:
CREATE OR REPLACE PROCEDURE total_compensation(IN salary DECIMAL(9, 2),
IN percent_bonus DECIMAL(4, 2),
IN stocks_value DECIMAL(9, 2),
OUT total_comp DECIMAL(9, 2))
BEGIN
SET total_comp = salary + salary * percent_bonus / 100 + stocks_value;
END;
/
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);
CALL total_compensation(23000, 3.4, 5000, :result);
PRINT result;
28782.00
This is completely unreadable!
Sometimes developers do the following:
CALL total_compensation(23000 /* Salary */, 3.4 /* Bonus_Percent */, 5000 /* Stocks_Value */, :result);
PRINT result;
28782.00
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,
stocks_value => 5000,
salary => 23000,
percent_bonus => 3.4);
PRINT result;
28782.00
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 parameters.
Maintainable code comes at a price.
The rule for named parameter invocation are quite simple:
- The routine must be a function or procedure that has been defined with named parameters.
Naming parameters has always been mandatory for SQL routines, but it is optional for external routine definitions (such as Java or C).
Built-in functions today (with few exceptions) have no named parameters.
- The argument can be any expression to a routine permitted in that context.
This includes parameter markers, host variables, some expression and even subqueries
- As mentioned earlier there is not required order in which the arguments must be specified
- You can start of with positional syntax and then switch to named syntax.
But once you have started using named syntax you must continue to the end.
So the following is allowed:
CALL total_compensation(23000, 3.4, total_comp => :result, stocks_value => 5000);
PRINT result;
28782.00
But this is not:
CALL total_compensation(23000, 3.4, stocks_value => 5000, :result);
SQL20483N Invalid use of named argument "STOCKS_VALUE" when invoking routine "TOTAL_COMPENSATION". Reason code: "2"
- A parameter cannot be specified twice.
This includes the case where you use a mixed form:
CALL total_compensation(23000, salary => 20000, bonus_percent => 3.4, total_comp => ?, stocks_value => 5000);
SQL0440N No authorized routine named "TOTAL_COMPENSATION" of type "PROCEDURE" having compatible arguments was found.
- Named parameter invocation can be used anywhere where functions and procedures can be invoked.
This includes queries, dynamic SQL, in procedures, SQL PL, PL/SQL and so on.
Downsides
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.
Conclusion
#Db2