How the EXECUTE FUNCTION Statement Works

For a user-defined function (UDF) to be executed with the EXECUTE FUNCTION statement, the following conditions must exist:
  • The qualified function name or the function signature (the function name with its parameter list) must be unique within the name space or database.
  • The function must exist in the current database.

If EXECUTE FUNCTION specifies fewer arguments than the user-defined function expects, the unspecified arguments are said to be missing. Missing arguments are initialized to their corresponding parameter default values, if these were defined. The syntax of specifying default values for parameters is described in Routine Parameter List.

EXECUTE FUNCTION returns an error under the following conditions:
  • EXECUTE FUNCTION specifies more arguments than the UDF expects.
  • One or more arguments are missing and do not have default values.
  • The fully qualified function name or the function signature is not unique.
  • No function with the specified name or signature that you specify is found.
  • EXECUTE FUNCTION attempts to invoke a user-defined procedure.
  • In a distributed transaction, a UDR that is running on a subordinate participating server calls a remote function on a database of another server instance.

If the function name is not unique within the database, you must specify enough parameter_type information to disambiguate the name. See the section Arguments for more information about how to specify parameters when invoking a function.

The specific name of an external UDR is valid in some DDL statements, but is not valid in contexts where you invoke the function.

If HCL OneDB™ cannot resolve an ambiguous function name whose signature differs from the signature of another routine only in an unnamed-ROW type parameter, an error is returned. (This error cannot be anticipated by the database server when the ambiguous function is defined.)

INTO Clause

(1)
INTO Clause

1  INTO
2.1+ ,
2.1  data_var? 1
2.2.1 :
2.2.1 2 $
2.2.1 INDICATOR
2.1 indicator_var
2.1 data_structure
Notes:
  • 1 ESQL/C only
  • 2 HCL OneDB extension

Element Description Restrictions Syntax
data_structure Structure that was declared as a host variable Individual elements of structure must be compatible with the data types of the returned values Language specific
data_var Variable to receive the value that a user-defined function returns See Data Variables. Language specific
indicator_var Program variable to store a return code if the corresponding data_var receives a NULL value Use an indicator variable if the value of the corresponding data_var might be NULL Language specific

You must include an INTO clause with EXECUTE FUNCTION to specify the variables that receive the values that a user-defined function returns. If the function returns more than one value, the values are returned into the list of variables in the order in which you specify them.

If the EXECUTE FUNCTION statement stands alone (that is, it is not part of a DECLARE statement and does not use the INTO clause), it must execute a noncursor function. A noncursor function returns only one row of values. The following example shows a SELECT statement in : 
EXEC SQL EXECUTE FUNCTION 
   cust_num(fname, lname, company_name) INTO :c_num;