Named Arguments and Defaults for Parameters - Procedures
Named and default parameters are supported for SQL and external procedures, making it possible to run more SQL applications on IBM i.
- Extend procedures without fear of breaking existing callers and simplify the calling requirements by adding default values.
- Procedure call invocation has the same type of support as CL commands.
Currently, when calling a procedure, all parameters to the procedure must be specified in the correct order and no parameter can be omitted from the CALL statement. When upgrading a procedure signature and adding an additional argument, all references to the procedure must be changed at the same time the procedure is modified to add any new arguments. This feature provides flexibility to procedures in that arguments can now be specified in any order that makes sense to the caller of the procedure by explicitly naming the parameter and the value supplied to that parameter. For arguments that are not supplied via the CALL statement, their value can now be provided as a default value supplied during the definition of the procedure.
This is particularly useful for modifying existing procedures to add new parameters. Default values can be added for the new parameters so that existing applications do not have to be modified. The existing CALL statements that do not have the new parameters will continue to run successfully with the provided defaults used for the unspecified parameters.
With this enhancement:
1) Parameters may be omitted if the routine was defined with a default value
2) Parameters may be specified in any order by specifying the name in the call
3) Works with LANGUAGE SQL and EXTERNAL procedures
CREATE PROCEDURE Add_New_Employee (Name CHAR(40), ID int DEFAULT (select NEXT VALUE from EmployeeIDs), Dept int DEFAULT 123, Date_Hired DEFAULT Current Date)
1. Omitting parameters - defaults are used for the unspecified parameters
CALL Add_New_Employee('John Doe')
2. Using Default keyword
CALL Add_New_Employee('John Doe',default,322,'06/23/2012')
3. Using a named parameter so that parameters can be specified out of order - defaults are used for the unspecified parameters
CALL Add_New_Employee('John Doe',Date_Hired=>'06/23/2012')
The CALL statement calls a procedure.
The CREATE PROCEDURE (External) statement defines an external procedure at the current server.
The CREATE PROCEDURE (SQL) statement creates an SQL procedure at the current server.
Given a procedure invocation, DB2 must decide which of the possible procedures with the same name to execute.
A procedure (often called a stored procedure) is a program that can be called to perform operations. A procedure can include both host language statements and SQL statements. Procedures in SQL provide the same benefits as procedures in a host language.
14 January 2020