SQL Procedure Support For UDTs
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
/* Implicit Cast To Back Up The FromSql Functionality */
CREATE CAST (address AS Varchar(100))
WITH SPECIFIC METHOD ToString
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 “NEW_VARIANT TYPE” in SQL Functions, Operators, Expressions, and Predicates). 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 SQL Stored Procedures and Embedded SQL.
You can SELECT INTO a UDT local variable. No expressions are allowed in the INTO list.
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.
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.