Parameter Names and Data Types | CREATE PROCEDURE | Teradata Vantage - 17.10 - Parameter Names and Data Types - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

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

The parameter list contains a list of variables to be passed to the external procedure. The list is bounded by open and closed parentheses even if there are no parameters to be passed.

Each parameter type is associated with a mandatory data type to define the type of the parameter passed to or returned by the external procedure. The specified data type can be any valid data type including TD_ANYTYPE and VARIANT_TYPE (see Teradata Vantage™ - Data Types and Literals, B035-1143 for a complete list of data types).

Note that you can only specify the VARIANT_TYPE data type for input parameters, but you can specify TD_ANYTYPE for all parameters. Character data types can also specify an associated CHARACTER SET clause.

By defining a procedure using TD_ANYTYPE as a parameter data type, you can overload the procedure based on its server character set or numeric precision rather than its name. When you define a procedure using TD_ANYTYPE, the database determines the parameter data type at execution time based on the parameters that are provided.

In addition to allowing TD_ANYTYPE to act as an alias for the type for parameter data types with undetermined attributes, you can also use TD_ANYTYPE to resolve all possible parameter data types. This allows you to develop fewer procedure signatures for the same basic procedure as well as to provide flexibility in coding the logic for the required procedure behavior.

However, using TD_ANYTYPE as a parameter data type results in the loss of the implicit conversions that automatically convert input values to those that match the procedure signature. As a result, you have a greater responsibility to make sure that any undefined parameters are properly resolved and processed.

See Teradata Vantage™ - Data Types and Literals, B035-1143 for more information about TD_ANYTYPE and see Teradata Vantage™ - SQL External Routine Programming, B035-1147 for more information about how to code external procedures to take advantage of the TD_ANYTYPE data type.

You cannot specify a character parameter data type with a server character set of KANJI1. Otherwise, the database aborts the request and returns an error to the requestor.

IF the external routine for the procedure is written in this language … THEN the maximum number of parameters you can specify in its parameter list is …
  • C
  • C++
256
Java 255

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

The following table summarizes the standard Teradata 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 procedure.

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

See PARAMETER STYLE Clause and Teradata Vantage™ - SQL External Routine Programming, B035-1147 for details on parameter passing conventions for the TD_GENERAL and SQL parameter styles.

See the documentation on the SQL Descriptor Area (SQLDA) in Teradata Vantage™ - SQL Stored Procedures and Embedded SQL, B035-1148 for a list of the data type encodings that procedure IN, INOUT, and OUT parameters can return to a client application.