Table-Valued Parameter Type Discovery

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

The consumer-that is, the client application using the SQL Server Native Client OLE DB Provider-can discover the type of each command parameter if the command text has been given to the OLE DB Provider. After the type of a table-valued parameter is known, the consumer can discover the metadata information for each individual column of the table-valued parameter.

The type information of procedure parameters is supported by ICommandWithParameters::GetParameterInfo for most parameter types. Beginning with SQL Server 2005 (9.x), with the introduction of user-defined types and the xml data type, the GetParameterInfo method was not sufficient for this purpose because it was not possible to provide user-defined type information (name, schema, and catalog) through ICommandWithParameters. A new interface, ISSCommandWithParameters, was defined to provide extended type information.

For table-valued parameters, you also use the ISSCommandWithParameters interface to discover detailed information. The client calls ISSCommandWithParameters::GetParameterInfo after preparing the command object. For table-valued parameters, the wType member of the DBPARAMINFO structure is set to DBTYPE_TABLE by the provider. The ulParamSize field of DBPARAMINFO structure has a value of ~0.

The consumer would then request additional properties (table-valued parameter type catalog name, table-valued parameter type schema name, table-valued parameter type name, column ordering, and default columns) by using ISSCommandWithParameters::GetParameterProperties.

After the type name is known, to retrieve the individual column information the consumer must either call IOpenRowset::OpenRowsetor obtain the DBSCHEMA_TABLE_TYPE_COLUMNS rowset by specifying the table-valued parameter type name as the table name.

See Also

Table-Valued Parameters (OLE DB)
Use Table-Valued Parameters (OLE DB)