IBM Support

Named Arguments and Defaults for Parameters - Procedures

News


Abstract

Named Arguments and Defaults for Parameters - Procedures

Content

You are in: IBM i Technology Updates > Db2 for i - Technology UpdatesDb2 for i Functional Enhancements > 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

Examples:

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')

 

Refer to the SQL Reference and SQL Programming books for usage information. The book updates are spread across several sections:
CALL
The CALL statement calls a procedure.

CREATE PROCEDURE (External)
The CREATE PROCEDURE (External) statement defines an external procedure at the current server.

CREATE PROCEDURE (SQL)
The CREATE PROCEDURE (SQL) statement creates an SQL procedure at the current server.

Procedure resolution
Given a procedure invocation, DB2 must decide which of the possible procedures with the same name to execute.

Stored procedures
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.

[{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB08","label":"Cognitive Systems"}}]

Document Information

Modified date:
14 January 2020

UID

ibm11167346