SQL Procedure Support For UDTs | CREATE PROCEDURE | Teradata Vantage - SQL Procedure Support For UDTs - Advanced SQL Engine - Teradata Database

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.