17.05 - 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.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1144-175K
Language
English (United States)
IN
Parameter is for 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 for output only.
INOUT
Parameter can be both input and output.
parameter_name
Name of a parameter or local variable that is replaced with an argument during procedure execution.
The maximum length of a parameter name is either 30 LATIN characters or 128 Unicode characters. For details about object name length limits, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
A maximum of 256 parameters can be specified with each procedure in a comma-separated list.
data_type
The data type of the parameter or local variable must be specified with each parameter_name.
You cannot specify a VARIANT_TYPE UDT as an IN parameter data type for an SQL procedure.
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.
Note that the system does not default to the data type you assign to an INOUT parameter at the time the procedure is created 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 the output parameter returned to an INOUT parameter cannot be contained by the default data type set for that parameter by the system. See CREATE PROCEDURE (External Form) in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 and CALL in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 for details.
For parameters, you can specify CHARACTER SET and either CASESPECIFIC or NOT CASESPECIFIC as an attribute of the character data type.
If you specify both, you must specify them in the following order.
  • CHARACTER SET
You cannot specify a character server data set of KANJI1. If you attempt to do so, Vantage aborts the request and returns an error to the requestor.
  • CASESPECIFIC or NOT CASESPECIFIC
You cannot specify other data type attributes, such as NOT NULL, UPPERCASE, or FORMAT, for SQL procedures.
The keyword DEFAULT is not allowed before the character set.
For local variables, you can specify CHARACTER SET and NOT CASESPECIFIC in any order after data_type.
If you do not specify CHARACTER SET, the character set defaults to the character set of the user creating or compiling the SQL procedure.
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.