SQL Procedure Support For UDTs | CREATE PROCEDURE | Teradata Vantage - 17.10 - SQL Procedure Support For UDTs - 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)

For correct operation of a UDT within a procedure, the UDT must have its mandatory ordering and transform functionality defined. Additionally, the tosql and fromsql transform routines must be duplicated by an equivalent set of predefined data type-to-UDT and UDT-to-predefined data type implicit cast definitions. You can do this by referencing the same routines in both the CREATE TRANSFORM and CREATE CAST statements.

For distinct UDTs, if you plan to use the system-generated functionality, no additional work is required because the transform and implicit casting functionality have already been defined. For structured UDTs, you must explicitly define the transform and cast functionality, as shown by the following examples.

/* Transform Functionality */
    CREATE TRANSFORM FOR address ClientIO (
      TO SQL WITH SPECIFIC FUNCTION SYSUDTLIB.stringToAddress,
      FROM SQL WITH SPECIFIC METHOD toString);

/* Implicit Cast To Back Up The ToSql Functionality */
    CREATE CAST ( Varchar(100) AS address )
      WITH SPECIFIC FUNCTION SYSUDTLIB.stringToAddress
      AS ASSIGNMENT;

/* Implicit Cast To Back Up The FromSql Functionality */
    CREATE CAST (address AS Varchar(100))
      WITH SPECIFIC METHOD  ToString
      AS ASSIGNMENT;

You can declare an input parameter to have the VARIANT_TYPE UDT data type only within the body of the procedure definition, which means that the system passes the dynamic UDT data type to this parameter position during runtime using the NEW VARIANT_TYPE expression (see Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210). You cannot declare an IN parameter data type for the procedure itself to have the VARIANT_TYPE data type.

You cannot declare the data type for any parameter to be TD_ANYTYPE.

Details about SQL procedure support for the following DML statements are documented in Teradata Vantage™ - SQL Stored Procedures and Embedded SQL, B035-1148.
  • SELECT INTO a UDT local variable.

    You can SELECT INTO a UDT local variable. No expressions are allowed in the INTO list.

  • FETCH INTO a UDT local variable.

    You can FETCH INTO a UDT local variable. No expressions are allowed in the INTO list.

    You cannot perform a FETCH INTO operation in which you attempt to fetch into a predefined type nor can you perform a fetch into operation that involves UDT types that are not 100 percent identical. In other words, no implicit casts are applied for these operations.

  • INSERT INTO a UDT local variable.

    You can INSERT INTO a UDT local variable in a table, but you cannot invoke methods on such a local variable if those methods are invoked within the INTO clause.