Using the DEFAULT Clause

Use the DEFAULT keyword followed by an expression to specify a default value for a parameter. If you provide a default value for a parameter, and the UDR is called with fewer arguments than were defined for that UDR, the default value is used. If you do not provide a default value for a parameter, and the UDR is called with fewer arguments than were defined for that UDR, the calling application receives an error.

The following example shows a CREATE FUNCTION statement that specifies a default value for a parameter. This function finds the square of the i parameter. If the function is called without specifying the argument for the i parameter, the database server uses the default value 0 for the i parameter.

CREATE FUNCTION square_w_default
   (i INT DEFAULT 0) {Specifies default value of i}
   RETURNING INT; {Specifies return of INT value}
   DEFINE j INT; {Defines routine variable j}
   LET j = i * i; {Finds square of i and assigns it to j}
   RETURN j; {Returns value of j to calling module}
END FUNCTION;

Warning: When you specify a date value as the default value for a parameter, make sure to specify 4 digits instead of 2 digits for the year. When you specify a 2-digit year, the DBCENTURY environment variable setting can affect how the database server interprets the date value, so the UDR might not use the default value that you intended. For more information, see the HCL OneDB™ Guide to SQL: Reference.