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.