parameter_specification - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Advanced SQL Engine
Teradata Database
Release Number
September 2020
English (United States)
Last Update
Product Category
Teradata Vantage™
A set of parameter names for the method.
The parameter list is a parenthetical comma-separated list of data types, including UDTs, and optional parameter names and locators for the variables to be passed to the method.
Parameter names must be unique within a method definition.
You cannot use the keyword SELF to name method parameters.
The maximum number of parameters a method accepts is 128.
You must specify opening and closing parentheses even if no parameters are to be passed to the function.
If you specify one parameter name, then you must specify names for all the parameters passed to the function.
If you do not specify parameter names, the system assigns unique names to them in the form P1, P2, …, P n. These names are used in the COMMENT statement (see COMMENT (Comment Placing Form)), displayed in the report produced by the HELP METHOD statement in the PARAMETER NAME column (see HELP METHOD), and appear in the text of error messages.
Whether you specify a list of parameter names or not, you must specify a set of data types for the parameters specified.
  • If a parameter in the parameter_name list has a predefined data type, the data type entry is that data type.
  • If the type specified as data type is a predefined character data type, the server_character_set entry is its server character set.
The predefined data type associated with each parameter is the type of the parameter or returned value. All predefined Vantage data types except the Period types are valid. Distinct and structured UDTs are valid for scalar UDFs only. Character data can also specify a CHARACTER SET clause.
For predefined data types that take a length or size specification, like BYTE, CHARACTER, DECIMAL, VARCHAR, and so on, the size of the parameter indicates the largest number of bytes that can be passed.
If a parameter has a LOB data type, you must specify a locator indication for it.
You cannot specify the server_character_set as KANJI1. Otherwise, Vantage returns an error to the requestor.
If a parameter in the parameter_name list has a UDT type, the UDT_name entry is the name of that UDT.
BLOB and CLOB types must be represented by a locator (see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 for a description of locators). Vantage does not support in-memory LOB parameters. An AS LOCATOR phrase must be specified for each LOB parameter and return value.
Note, however, that whenever a LOB that requires data type conversion is passed to a method, the LOB must be materialized for the conversion to take place.