17.10 - Behavior When Using NULL as a Literal Argument - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1147-171K
Language
English (United States)

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 a Function That is Overloaded.