The RETURNS NULL ON NULL INPUT and CALLED ON NULL INPUT options in the CREATE FUNCTION statement determine what happens if the NULL keyword is used as any of the input arguments.
| IF an input argument is the NULL keyword and the corresponding CREATE FUNCTION statement specifies … | AND the parameter style is … | THEN … |
|---|---|---|
| RETURNS NULL ON NULL INPUT | SQL or TD_GENERAL | the function is not evaluated and the result is always NULL. |
| CALLED ON NULL INPUT | SQL | the function is called with the appropriate indicators set to the null indication. |
| TD_GENERAL | an error is reported. |
For table functions, the parameter style is always SQL.
If the CREATE FUNCTION statement does not specify either RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUT, the default is CALLED ON NULL INPUT.
NULL as a literal argument is compatible with a parameter of any data type. For example, consider the following scalar function:
CREATE FUNCTION sales( p1 INTEGER, p2 DECIMAL(2,0), p3 VARCHAR(20) ) RETURNS INTEGER LANGUAGE C NO SQL PARAMETER STYLE SQL EXTERNAL;
You can successfully pass the NULL keyword as any function argument:
SELECT sales(3, NULL, 'UPD PRD-3011'); SELECT sales(NULL, NULL, NULL);
Passing the NULL keyword as an argument to overloaded functions can result in errors unless Vantage can identify which function to invoke without ambiguity. For details, see Calling an Overloaded Function.