17.10 - parameter_specification - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

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

A parenthetical comma-separated list of data types, including UDTs, and parameter names for the variables to be passed to the procedure.

You must specify opening and closing parentheses even if no parameters are to be passed to the procedure.

The maximum number of parameters you can specify in the parameter list depends on the language in which the external routine for the procedure is written:
Language Maximum Number of Parameters
C or C++ 256
Java 255
IN
Parameter is input only. IN is the default parameter type. If the parameter type is not specified, the parameter is assumed to be of the IN type.
OUT
Parameter is output only.
INOUT
Parameter can be input and output.
Vantage does not default to the data type you assign to an INOUT parameter when the procedure is called. Instead, it defaults to the smallest data type that can contain the specified input parameter. As a result, memory overflow errors can occur if an output parameter returned to an INOUT parameter cannot be contained by the default data type set for that parameter by the system.
parameter_name
Name of parameter name to pass to the procedure.
data_type
The data type associated with each parameter is the type of the parameter. For C and C++ procedures, all Vantage data types are valid. For Java procedures, all Vantage data types are valid except GRAPHIC and VARGRAPHIC.
For data types that take a length or size specification, like BYTE, CHARACTER, DECIMAL, VARCHAR, and so on, the length of the parameter indicates the longest string that can be passed.
Character data can also specify a CHARACTER SET clause.
You cannot specify a character server data set of KANJI1. Otherwise, the system returns an error to the requestor.
BLOB and CLOB types must be represented by a locator. For a description of locators, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146. The system does not support in-memory LOB parameters. An AS LOCATOR phrase must be specified for each LOB parameter and return value in a non-Java procedure.
For Java procedures, the simple data type references BLOB and CLOB implicitly refer to BLOB AS LOCATOR and CLOB AS LOCATOR, respectively. Because of this, you cannot specify an explicit AS LOCATOR for BLOB and CLOB columns in Java procedures.
When a LOB that requires data type conversion is passed to an external procedure, the LOB must be materialized for the conversion to take place.
VARIANT_TYPE
You can only specify the VARIANT_TYPE UDT type for callable input parameters within the body of the procedure using a NEW VARIANT_TYPE expression to pass dynamic UDTs into UDFs. You cannot declare a VARIANT_TYPE as an IN parameter data type for a procedure.
TD_ANYTYPE
You can specify the system-defined TD_ANYTYPE data type for an IN, OUT, or INOUT parameter.
See Teradata Vantage™ - SQL Stored Procedures and Embedded SQL, B035-1148 for a list of the data type encodings that SQL procedure IN, INOUT, and OUT parameters can return to a client application.