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 in an input argument.
| CREATE FUNCTION Clause | Parameter Style | Result |
|---|---|---|
| RETURNS NULL ON NULL INPUT | SQL or TD_GENERAL | Function is not evaluated and result is NULL. |
| CALLED ON NULL INPUT | SQL | Function is called with appropriate indicators set to null indication. |
| TD_GENERAL | 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.