Schema Collections

The ProcedureParameters schema returns metadata for Procedure parameters. The notable metadata columns for this schema collection are:

The UserDefinedFunctionParameters schema returns metadata for Teradata user defined function parameters. The notable metadata columns for this schema collection are:

Configuring Parameters

Configuring TD_ANYTYPE parameters is different than other TdType parameters, where the data type is known by the database (as defined by the stored procedure). TD_ANYTYPE is a placeholder that must be defined in the external stored procedure. By defining TdParameter.TdType to TdType.AnyType, the CALL statement by the .NET Data Provider generates an additional clause transmitted to the database to define the OUT parameter (when setting Command.CommandType to CommandType.StoredProcedure). The actual database data type is defined by TdParameter.SecondaryTdType and is included within the additional clause to the database.

The following example shows how to configure a TD_ANYTYPE parameter in an external stored procedure. Depending upon the desired SecondaryTdType, the TdParameter.Size, TdParameter.Precision and TdParameter.Scale must be set. The example assumes this definition for an external stored procedure:

REPLACE PROCEDURE xspAnyTypeProcedure(IN p1 INTEGER, OUT result TD_ANYTYPE).

public static void ExampleTdAnyTypeParameters(TdCommand cmd)
{
   // REPLACE PROCEDURE xspAnyTypeProcedure(IN a INTEGER, OUT result TD_ANYTYPE)
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.CommandText = "xspAnyTypeProcedure";

   cmd.Parameters.Add(null, TdType.Integer);
   cmd.Parameters[0].Direction = Parameter.Direction.Input;
   cmd.Parameters[0].Value = 4;

   cmd.Parameters.Add(null, TdType.AnyType);
   cmd.Parameters[1].Direction = ParameterDirection.Output;
   cmd.Parameters[1].SecondaryTdType = TdType.VarChar;
   cmd.Parameters[1].Size = 20;

   cmd.ExecuteNonQuery();

   // Going to process data of output parameter
   String result = (String)cmd.Parameters[1].Value;
}

Retrieving TD_ANYTYPE Parameters

TD_ANYTYPE represents a placeholder for a runtime dynamically returned data type. A TD_ANYTYPE data type is not a predefined SQL data type. The database always transmits data as predefined SQL data types and therefore the .NET Data Provider application must explicitly define the OUT parameter data types or the database may possibly infer the return data type from the TD_ANYTYPE IN or INOUT parameter.

See Also

Data Type Mappings

Configuring Parameters and Parameter Data Types

External Stored Procedures and TD_ANYTYPE Parameters