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;

In Extended Parallel Server, to re-create this example, use the CREATE PROCEDURE statement instead of the CREATE FUNCTION statement.

Extended Parallel Server supports no more than a single OUT parameter. If one is specified, it must be the last item in the parameter list.