Edit

OLE DB Table-Valued Parameter Type Support in SQL Server Native Client (Methods)

  • Article

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

The following standard OLE DB methods support table-valued parameters:

Method Table-valued parameter support
ITableDefinitionWithConstraints::CreateTableWithConstraints Used when you know type information of table-valued parameter, and want to instantiate a table-valued parameter rowset object based on the type-information.

For more information, see "Static Scenario" in Table-Valued Parameter Rowset Creation.
IOpenRowset::OpenRowset Used when you do not know the type information of a table-valued parameter, and want to instantiate a table-valued parameter rowset object based on metadata information retrieved from the server.

For more information, see "Dynamic Scenario" in Table-Valued Parameter Rowset Creation.
ISSCommandWithParameters::SetParameterInfo To specify a table-valued parameter command parameter, the consumer specifies the type of the parameter as "table" or "DBTYPE_TABLE" in the pwszName member of DBPARAMBINDINFO structure. The ulParamSize is set to ~0. For more information, see "Table-Valued Parameter Specification" in Executing Commands Containing Table-Valued Parameters.
ISSCommandWithParameters::SetParameterProperties Sets properties specific to table-valued parameters, such as schema name, type name, column order, and default columns.

The consumer specifies the ordinal of the parameter in the iOrdinal of the SSPARAMPROPS structure. The property set requested is DBPROPSET_SQLSERVERPARAMETER.
ISSCommandWithParameters::GetParameterInfo Gets the types of all the parameters to a specified command.

For table-valued parameters, the wType field in DBPARAMINFO structure will have type DBTYPE_TABLE. The ulParamSize field will be set to ~0 to indicate unknown length.
ISSCommandWithParameters::GetParameterProperties Gets additional type information for parameters of the DBTYPE_TABLE type.

The consumer specifies the ordinal of the parameter in the iOrdinal member of the SSPARAMPROPS structure. The consumer can request any of the properties in the DBPROPSET_SQLSERVERPARAMETER property set that are listed under ISSCommandWithParameters::SetParameterProperties.

Because the consumer does not know the table-valued parameter type, the provider must set the SSPROP_PARAM_TYPE_TYPENAME, SSPROP_PARAM_TYPE_SCHEMANAME, and SSPROP_PARAM_TYPE_CATALOGNAME to their correct values. The remaining properties, SSPROP_PARAM_TABLE_DEFAULT_COLUMNS and SSPROP_PARAM_TABLE_COLUMN_SORT_ORDER, will have their default values. After the consumer has discovered the table-valued parameter type name, it uses IOpenRowset::OpenRowset to create an instance of this table-valued parameter, specifying the name of table-valued parameter type. For more information, see Table-Valued Parameter Type Discovery.
IRowsetInfo::GetProperties Gets table-valued parameter rowset properties. The consumer can use these properties to optimally set up bindings.
IColumnsRowset::GetColumnsRowset Retrieves metadata information about a SQL Server table. For table-valued parameters, this same interface provides detailed metadata information about each column, such as the following:

DBCOLUMN_FLAGS indicates nullability through the DBCOLUMNFLAGS_ISNULLABLE bit.

DBCOLUMN_ISUNIQUE indicates whether the column is an identity column.

DBCOLUMN_COMPUTEMODE indicates whether the column is computed.
IAccessor::CreateAccessor To bind a table-valued parameter rowset object to a command parameter, you create an accessor with its wType member set to DBTYPE_TABLE. The DBOBJECT structure will contain IID_IRowset or any other valid rowset object interface in the iid member. The rest of the fields are treated similarly to DBTYPE_IUNKNOWN.

See Also

OLE DB Table-Valued Parameter Type Support
Table-Valued Parameter Rowset Creation
Use Table-Valued Parameters (OLE DB)