SQL Server talbe valued parameters (TVP)

Table value parameter (ADO. net) in SQL Server 2008)

Table value parameters provide a way to applyProgramTo SQL Server, instead of multiple round-trips or special server logic.You can use table value parameters to encapsulate data rows in client applications and send data to the server using a single parameterized command.The input data rows are stored in a table variable. You can use Transact-SQL to operate the table variable.

You can use standard select statements to access the column values in Table value parameters.If the table value parameter is of a strong type, its structure is automatically verified.The table value parameter size is limited only by the server memory.

Description
Data cannot be returned in Table value parameters.Table value parameters are only input parameters. Output keywords are not supported.

For more information about table value parameters, see the following resources.

Resources	Description
Table-valued parameters (Database Engine) (Table value parameter [Database Engine]), located in SQL Server books online	Describes how to create and use table value parameters.
User-Defined table types (User-Defined table type), located in SQL Server books online	Description: The user-defined table type used to declare Table value parameters.
Microsoft SQL Server database engine (Microsoft SQL Server database engine) Section of codeplex	Examples that demonstrate how to use SQL server features and functions are provided.

Pass multiple lines in earlier versions of SQL Server

Before introducing Table value parameters in SQL Server 2008, the options used to pass multiple rows of data to stored procedures or parameterized SQL commands are limited.Developers can choose to use the following options to pass multiple rows to the server:

• Use a series of single parameters to represent values in multiple data columns and rows.The amount of data transmitted using this method is limited by the number of allowed parameters.The SQL Server process can have a maximum of 2100 parameters.You must use the server logic to combine these individual values into Table variables or temporary tables for processing.

• Bind multiple data values to a separator string or XML document, and then pass these text values to the process or statement.This process requires that the corresponding process or statement include the logic required to verify the data structure and cancel the bundled value.

• Create a series of single SQL statements for data modifications that affect multiple rows, for example, by callingSqldataadapterOfUpdateMethod.You can submit the changes to the server separately or as a group for batch processing.However, even if the statement is submitted in batches containing multiple statements, each statement is still executed independently on the server.

• UseBCPUtility orSqlbulkcopyThe object loads many rows of data into the table.Although this technology is very effective, it does not support server-side processing unless it loads data into a temporary table or table variable.

Table value parameter type

The table value parameter is based on the strong type table structure defined by using the create type Statement of transact-SQL.You must create a table type and define the structure in SQL Server before using the table value parameter in the client application.For more information about creating table types, see user-defined table types in SQL Server 2008 books online ).

The following statement creates a table Type named categorytabletype, including the categoryid and categoryname columns:

 
Create type DBO. categorytabletype as table (categoryid int, categoryname nvarchar (50 ))
 

After creating a table type, you can declare the table value Parameters Based on the type.The following Transact-SQL snippet demonstrates how to declare Table value parameters in the stored procedure definition.Note that you must use the readonly keyword when declaring Table value parameters.

 
Create procedure usp_updatecategories (@ tvpnewcategories DBO. categorytabletype readonly)
 

Modify data using Table value parameters (TRANSACT-SQL)

Table value parameters can be used in set-based data modification. These data modifications can affect multiple rows by executing a single statement.For example, you can select all rows in the Table value parameter and insert them into the database table. You can also create an update Statement by connecting the table value parameter to the table to be updated.

The following Transact-SQL update statement demonstrates how to use a table value parameter by connecting it to the categories table.When using the table value parameter with join in the from clause, you must also provide an alias for it. As shown in this example, the alias of the Table value parameter is "EC ":

 
Update DBO. categories set categories. categoryname = EC. categoryname from DBO. Categories inner join @ tvpeditedcategories as EC on DBO. categories. categoryid = EC. categoryid;
 

This example shows how to select rows from Table value parameters to execute insert in a single set-based operation.

Copy

 
Insert into DBO. Categories (categoryid, categoryname) Select NC. categoryid, NC. categoryname from @ tvpnewcategories as NC;
 

Table value parameter restrictions

The following are restrictions on Table value parameters:

• Table value parameters cannot be passed to user-defined functions.

• Only the table value parameters can be indexed to support the unique or primary key constraints.SQL Server does not maintain statistics on Table value parameters.

• In Transact-SQLCodeThe table value parameter is read-only.The column value in the row where the table value parameter cannot be updated and the row cannot be inserted or deleted.To modify the data transmitted to the stored procedure or parameterized statement in the Table value parameter, you must insert the data into the temporary table or table variable.

• You cannot use the alter table statement to modify the design of Table value parameters.

Sqlparameter configuration example

system. data. sqlclient supports data from able , dbdatareader , or ilist Object fill Table value parameters. must be http://www.w3.org/1999/xhtml by using sqlparameter "> typename attribute specifies the type name of the Table value parameter. typename the name must match the name of the compatible type previously created on the server. the following code snippet demonstrates how to configure sqlparameter to insert data.

// Configure the command and parameter.Sqlcommand insertcommand =NewSqlcommand (sqlinsert, connection); sqlparameter tvpparam = insertcommand. Parameters. addwithvalue ("@ Tvpnewcategories", Addedcategories); tvpparam. sqldbtype = sqldbtype. Structured; tvpparam. typename ="DBO. categorytabletype";
 

You can also useDbdatareaderAny object derived from, process the data row stream to the table value parameter, as shown in this code segment:

 // Configure the sqlcommand and table-valued parameter.Sqlcommand insertcommand =NewSqlcommand ("Usp_insertcategories", Connection); insertcommand. commandtype = commandtype. storedprocedure; sqlparameter tvpparam = insertcommand. Parameters. addwithvalue ("@ Tvpnewcategories", Datareader); tvpparam. sqldbtype = sqldbtype. Structured;
 

Pass the table value parameter to the stored procedure

This example shows how to pass the table value parameter data to the stored procedure. Sample Code by usingGetchangesMethod to extract the added rows to the newDatatable. Then, the sample code definesSqlcommand, AndCommandtypeSet propertyStoredprocedure. Sample Code by usingAddwithvalueMethod pairSqlparameterFill andSqldbtypeSetStructured. then, execute executenonquery by using the sqlcommand method.

// Assumes connection is an open sqlconnection object.Using(Connection ){// Create a datatable with the modified rows.Datatable addedcategories = categoriesdatatable. getchanges (datarowstate. Added );// Configure the sqlcommand and sqlparameter.Sqlcommand insertcommand =NewSqlcommand ("Usp_insertcategories", Connection); insertcommand. commandtype = commandtype. storedprocedure; sqlparameter tvpparam = insertcommand. Parameters. addwithvalue ("@ Tvpnewcategories", Addedcategories); tvpparam. sqldbtype = sqldbtype. Structured;// Execute the command.Insertcommand. executenonquery ();}
 

Passing Table value parameters to parameterized SQL statements

the following example shows how to use a select subquery (with table value parameters as data sources) insert statement to insert data into DBO. in the categories table. when you pass the table value parameter to a parameterized SQL statement, you must specify the type name of the Table value parameter by using the new sqlparameter typename property. typename must match the name of the compatible type previously created on the server. the code in this example uses typename attribute to reference DBO. the type structure defined in categorytabletype.

Description
If a value is provided for the identity column in the Table value parameter, the set identity_insert statement must be issued for the session.

 // Assumes connection is an open sqlconnection.  Using (Connection ){ // Create a datatable with the modified rows. Datatable addedcategories = categoriesdatatable. getchanges (datarowstate. Added ); // Define the insert-select statement.  String Sqlinsert = "Insert into DBO. Categories (categoryid, categoryname )" + "Select NC. categoryid, NC. categoryname" + "From @ tvpnewcategories as NC ;"  // Configure the command and parameter. Sqlcommand insertcommand = New Sqlcommand (sqlinsert, connection); sqlparameter tvpparam = insertcommand. Parameters. addwithvalue ( "@ Tvpnewcategories" , Addedcategories); tvpparam. sqldbtype = sqldbtype. Structured; tvpparam. typename = "DBO. categorytabletype" ; // Execute the command. Insertcommand. executenonquery ();}

 

Stream processing of rows using datareader

You can also useDbdatareaderTo process the data row stream to Table value parameters. The following code snippet demonstrates how to useOraclecommandAndOracledatareaderTo retrieve data in the Oracle database. Then, configure the sample codeSqlcommandTo call the stored procedure with a single input parameter. SqlparameterOfSqldbtypeSet propertyStructured. AddwithvalueSetOracledatareaderThe result set is passed to the stored procedure as a table value parameter.

// Assumes connection is an open sqlconnection.
// Retrieve data from Oracle.
Oraclecommand selectcommand =NewOraclecommand (
"Select categoryid, categoryname from categories ;",
Oracleconnection );
Oracledatareader extends lereader = selectcommand. executereader (
Commandbehavior. closeconnection );

// Configure the sqlcommand and table-valued parameter.
Sqlcommand insertcommand =NewSqlcommand (
"Usp_insertcategories", Connection );
Insertcommand. commandtype = commandtype. storedprocedure;
Sqlparameter tvpparam =
Insertcommand. Parameters. addwithvalue (
"@ Tvpnewcategories", Oraclereader );
Tvpparam. sqldbtype = sqldbtype. Structured;

// Execute the command.
Insertcommand. executenonquery ();

 

Reference: http://msdn.microsoft.com/zh-cn/library/bb675163.aspx