Table-Valued Parameters (ODBC)

ODBC support for table-valued parameters lets a client application send parameterized data to the server more efficiently, by sending multiple rows to the server with one call.

For information about table-valued parameters on the server, see Table-Valued Parameters (Database Engine).

In ODBC, there are two ways that you can send table-valued parameters to the server:

  • All the table-valued parameter data can be in memory at the time SQLExecDirect or SQLExecute is called. This data is stored in arrays if there are multiple rows in the table-value.

  • An application can specify data-at-execution for a table-valued parameter when SQLExecDirect or SQLExecute is called. In this case, rows of data for the table-value can be provided in batches, or one at a time to reduce memory requirements.

The first option enables stored procedures to encapsulate more business logic. For example, a single stored procedure could encapsulate a whole order entry transaction when the order items are passed as a table-valued parameter. This option is very efficient, because only a single round trip to the server is required. Alternatively, you could use different procedures to handle the order header and order items separately, which would require more code and a more complex contract between the client and server.

The second method provides an efficient mechanism for bulk operations with very large amounts of data. This enables an application to stream rows of data to the server without having to buffer them all in memory first.

You can create constraints and primary keys when you create the table variable. Constraints are a good way to ensure that the data in a table meets specific requirements.

Uses of ODBC Table-Valued Parameters

Describes the primary user scenarios for table-valued parameters and ODBC.

ODBC SQL Type for Table-Valued Parameters

Describes the SQL_SS_TABLE type. This is a new ODBC SQL type that supports table-valued parameters.

Table-Valued Parameter Descriptor Fields

Describes descriptor fields that support table-valued parameters.

Descriptor Fields for Table-Valued Parameter Constituent Columns

Describes descriptor fields that have meaning for table-valued parameters.

Table-Valued Parameter Diagnostic Record Fields

Describes two diagnostic fields that have been added to diagnostic records to support table-valued parameters.

Statement Attributes that Affect Table-Valued Parameters

Describes a new descriptor header field that enables table-valued parameters columns to be addressed.

Binding and Data Transfer of Table-Valued Parameters and Column Values

Describes parameter binding and how to pass a table-valued parameter to the server.

Table-Valued Parameter Metadata for Prepared Statements

Describes how an application can obtain metadata for a prepared procedure call.

Additional Table-Valued Parameter Metadata

Describes how to use SQLProcedureColumns, SQLTables, and SQLColumns to retrieve metadata for a table-valued parameter.

Table-Valued Parameter Data Conversion and Other Errors and Warnings

Describes how to process errors on table-valued parameter column values.

Cross-Version Compatibility

Describes conflicts that can occur when table-valued parameters are used by a client or server of a version earlier than SQL Server 2008.

ODBC Table-Valued Parameter API Summary

Lists the ODBC functions that support table-valued parameters.

ODBC Table-Valued Parameter Programming Examples

Describes how to perform common tasks.