Parameter Names and Data Types - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

The parameter list contains a list of variables to be passed to the function.

Function parameters must be explicitly named. Parameter names are standard SQL identifiers.

The data types of SQL expressions passed as arguments must match those of their corresponding parameter data types in the definition of the SQL function. If the types do not match, Teradata Database returns an error to the requestor.

You cannot specify TD_ANYTYPE, VARIANT_TYPE, or TABLE as parameter data types for an SQL function.

The only exception to this is the case where the sizes of the parameter and argument data types differ, but the types themselves belong to the same category of data types such as numeric types, character types, DateTime types, and so on. In this case, a mismatch is tolerated as long as the argument size is less than the parameter size. For example, when the parameter type is INTEGER and the argument type is BYTEINT, the types are treated as being compatible. However, if the parameter type were BYTEINT and the argument type were INTEGER, the mismatch would abort the request and return an error to the requestor.

For example, the argument and parameter types are a mismatch in the following SQL UDF definition, but because the size of the argument type is less than the corresponding parameter type, the types are compatible.

     CREATE FUNCTION df2.myudf (a INTEGER, b INTEGER, c INTEGER)
     RETURNS INTEGER
     LANGUAGE SQL
     DETERMINISTIC
     SQL SECURITY DEFINER
     COLLATION INVOKER
     INLINE TYPE 1
     RETURN a*b*c;

     CREATE TABLE t1 (
       a1 BYTEINT,
       b1 INTEGER);
     SELECT df2.myudf(t1.a1, t1.b2, 2)
     FROM t1;

The contrary result is seen for the following example, where the only difference is that column a1 is defined with an INTEGER type rather than a BYTEINT type. Because the argument type is greater than its corresponding UDF parameter type in the following example, the SELECT request aborts and returns an error to the requestor.

     CREATE FUNCTION df2.myudf (a BYTEINT, b INTEGER, c INTEGER)
     RETURNS INTEGER
     LANGUAGE SQL
     DETERMINISTIC
     SQL SECURITY DEFINER
     COLLATION INVOKER
     INLINE TYPE 1
     RETURN a*b*c;

     CREATE TABLE t1 (
       a1 INTEGER,
       b1 INTEGER);
     SELECT df2.myudf(t1.a1, t1.b2, 2)
     FROM t1;

To make this example work correctly, you would need to explicitly cast t1.a1 as BYTEINT in the SELECT request, because an INTEGER argument does not fit into the BYTEINT parameter a as defined by the UDF df2.myudf.

Parameter names are used by the COMMENT statement (see COMMENT (Comment Placing Form)) and are reported by the HELP FUNCTION STATEMENT statement (see HELP FUNCTION). Parameter names, with their associated database and function names, are also returned in the text of error messages when truncation or overflow errors occur with a function call.

Each parameter type is associated with a mandatory data type to define the type of the parameter passed to or returned by the function. The specified data type can be any valid data type, including UDTs (see Teradata Vantage™ - Data Types and Literals, B035-1143 for a complete list of data types). Character data types can also specify an associated CHARACTER SET clause.

For character string types like VARCHAR that might have a different length depending on the caller, the length of the parameter in the definition indicates the longest string that can be passed. If there is an attempt to pass a longer string, the result depends on the session mode.

You cannot specify a character data type that has a server character set of KANJI1.

The following table summarizes the standard Teradata Database session mode semantics with respect to character string truncation:

IF the session mode is … THEN …
ANSI any pad characters in the string are truncated silently and no truncation notification is returned to the requestor.

A truncation exception is returned whenever non-pad characters are truncated.

If there is a truncation exception, then the system does not call the function. The relevant indicator values are not set to the number of characters truncated.

Teradata the string is truncated silently and no truncation notification message is returned to the requestor.