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.

In This Section

• 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.

See Also

Concepts

SQL Server 2008 R2 Native Client (ODBC)

Table-Valued Parameters (SQL Server Native Client)