Comparing Arguments to the Parameter List

When you create or register a UDR with CREATE PROCEDURE or CREATE FUNCTION, you declare a parameter list with the names and data types of the parameters that the UDR expects. (Parameter names are optional for external routines written in the C or Java languages.) See Routine Parameter List for details of declaring parameters.

User-defined routines can be overloaded, if different routines have the same identifier, but have different numbers of declared parameters. For more information about overloading, see Routine Overloading and Naming UDRs with a Routine Signature (IDS).

If you attempt to execute a UDR with more arguments than the UDR expects, you receive an error.

If you invoke a UDR with fewer arguments than the UDR expects, the omitted arguments are said to be missing. The database server initializes missing arguments to their corresponding default values. This initialization occurs before the first executable statement in the body of the UDR.

If missing arguments have no default values, Dynamic Server issues an error. Extended Parallel Server can invoke a routine with missing arguments that have no default values, but the routine might fail if a missing argument causes another error (for example, an undefined value for a variable).

Named parameters cannot be used to invoke UDRs that overload data types in their routine signatures. Named parameters are valid in resolving non-unique routine names only if the signatures have different numbers of parameters:

         func( x::integer, y );    -- VALID if only these 2 routines
         func( x::integer, y, z ); -- have the same 'func' identifier

         func( x::integer, y );    -- NOT VALID if both routines have
         func( x::float, y ;       -- same identifier and 2 parameters

For both ordinal and named parameters, the routine with the fewest parameters is executed if two or more UDR signatures have multiple numbers of defaults:

         func( x, y default 1 )
         func( x, y default 1, z default 2 )

If two registered UDRs that are both called func have the signatures shown above, then the statement EXECUTE func(100) invokes func(100,1).

You cannot supply a subset of default values using named parameters unless they are in the positional order of the routine signature. That is, you cannot skip a few arguments and rely on the database server to supply their default values.

For example, given the signature:

         func( x, y default 1, z default 2 )

you can execute:

        func( x=1, y=3 )

but you cannot execute:

         func( x=1, z=3 )