Parameter Names and Data Types | CREATE PROCEDURE | Teradata Vantage - Parameter Names and Data Types - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
jbg1472252759029
lifecycle
latest
Product Category
Teradata Vantage™

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.