Table-Valued Parameter Type Discovery


System_CAPS_ICON_warning.jpg Warning

SQL Server Native Client (SNAC) is not supported beyond SQL Server 2012. Avoid using SNAC in new development work, and plan to modify applications that currently use it. The Microsoft ODBC Driver for SQL Server provides native connectivity from Windows to Microsoft SQL Server and Microsoft Azure SQL Database.

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, 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 paramters, 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 ISSCommandWithParamters::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.

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

Community Additions