15.00 - Parameter Names and Data Types - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Parameter Names and Data Types

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 SQL Data Types and Literals 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, Teradata 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 SQL Data Types and Literals for more information about TD_ANYTYPE and see SQL External Routine Programming 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, Teradata 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)” on page 203 and SQL Data Definition Language for descriptions) and are reported by the HELP PROCEDURE statement (see “HELP PROCEDURE” on page 941). 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 Database 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” on page 438 and SQL External Routine Programming for details on parameter passing conventions for the TD_GENERAL and SQL parameter styles.

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