Defining UDFs with default parameters

You can define parameters for UDFs to have default values.

A default value for a parameter means that the parameter is not required on the function invocation. Using defaults allows flexibility by not requiring every invocation of a function to pass all parameter values. This can be useful when modifying existing functions.

A default can be a simple value such as a constant or the null value. It can also be an SQL expression, so it can be very complex. A default cannot be defined using the value of another parameter as part of its default expression. Any objects referenced in a parameter default must exist when the function is created.

Suppose you have a table function that is passed one parameter that is a project number and returns all rows from the project activity table that are registered with that number. If you get a request to limit the number of rows returned to a range of start dates, that is easy to do by adding default parameters for a date range.

CREATE FUNCTION ActProj (ProjNum CHAR(6),
                         StartAfterDate DATE DEFAULT NULL,
                         StartBeforeDate DATE DEFAULT NULL)
           RETURNS TABLE (PROJNO CHAR(6),
                          ACTNO SMALLINT,
                          STARTDATE DATE)

The function logic needs to be modified to accept two new parameters that might contain dates or might be the NULL value. Any invocation of the function that does not have a reason to pass a date range does not need to be changed. It would continue to look like this:

ActProj(:projnum)

Any application that needs the date range can pass one or both of the date parameters:

ActProj(:projnum, :StartDate, :EndDate)

Defaults can be defined for SQL or external functions.