Create and use a table parameter value (TVP) in the T-SQL (from: http://www.ixpub.net/thread-2751953-1-1.html)

I. Summary

Table-valued parameters (TVP) is a new feature introduced in SQL Server 2008. It provides a built-in method, the client application can send multiple rows of data to the SQL server by using only one SQL statement with the number of parameters.

II. Introduction

Before the appearance of Table value parameters, when sending multiple rows of data to SQL Server, we can only use some alternative solutions to achieve this:

(1) Use a series of independent parameters to represent values of multiple columns and multiple rows of data.

Using this method, the total amount of data that can be passed is limited by the number of available parameters. SQL Server Stored Procedures can use up to 2100 parameters.

In this method, the server logic must combine these independent values into Table variables or temporary tables for processing.

(2) bind multiple data values to a character string or XML document with a qualifier, and then pass the text values to a stored procedure or statement.

This method requires that the stored procedure or statement requires the necessary data structure verification and data unbinding logic.

(3) create a series of independent SQL statements for the modification of multi-row data.

Like those generated when the update method is called in a sqldataadapter, these updates can be submitted independently or in batches to the server.

However, although multiple statements are contained in batch submission, these statements are executed independently on the server.

(4) use the BCP utility or the sqlbulkcopy object to load multiple rows of data into a table.

Although this technology is highly efficient, it does not support execution on the server (Note: multiple rows of data cannot be transmitted to the stored procedure at a time ), unless the data is loaded into a temporary table or table variable.

The T-SQL feature in SQL Server 2008 adds Table value parameters. With this new feature, we can easily pass a table as a parameter to a function or stored procedure through a T-SQL statement or through an application.

(1) The table value parameter indicates that you can pass a table type as a parameter to a function or stored procedure.

(2) The table value parameter function allows you to import data to a table declared as a T-SQL variable, and then pass the table as a parameter to the stored procedure or function.

(3) Advantages of Table value parameters: You can send multiple rows of data to stored procedures or functions, you do not need to declare multiple parameters or use the XML parameter type to process multiple rows of data as before.

Iii. Description

The scheduler consists of three parts to describe the application of Table value parameters.

(1) create and use TVP in the T-SQL

(2) Use the datatable object in ADO. NET and pass it as a parameter to the stored procedure

(3) Use collection objects in ADO. NET and pass them as parameters to the stored procedure

Iv. Part 1: creating and using TVP in the T-SQL

See URL: MS-help: // Ms. sqlcc. V10/ms. sqlsvr. v10.en/s10de_1devconc/html/5e95a382-1e01-4c74-81f5-055612c2ad99.htm

1. Table value parameters have two main parts: SQL Server Type and parameters that reference this type. to create and use table value parameters, perform the following steps:

(1) create a table type and define the table structure.

The TVP function is based on the latest User-Defined table types (udtt) in sql2008. It allows users to register table definitions as the global sequence type.

After registration, these table types can be used in the batch processing and stored procedure function bodies like local variables, that is, udtt variables can be used as parameters in Stored Procedures and parameterized tsql.

There are many restrictions on the use of user-defined table types:

(1) a user-defined table type cannot be used to define the column type of the table, nor can it be used to define a field of the user-defined structure type.

(2) creating a non-aggregate index on a user-defined table type is not allowed, unless this index is a primary key or unique constraint created based on the user-defined table type.

(3) The default value cannot be specified in the definition of the User-Defined table type.

(4) Once created, you cannot modify the definition of the User-Defined table type.

(5) user-defined functions cannot be called using calculation column definitions in user-defined table types as parameters.

(6) a user-defined table type cannot be used as a table-valued parameter to call user-defined functions.

For example:

1. /* Create a user-defined table type */
2. Create type orderitem $ UDT as table (
3. Orderid int primary key,
4. Customerid int,
5. Orderedat datetime
6. )
7. Go

Copy code

(2) declare a routine with table type parameters.

1. --------------------------
2. Create procedure orderitem $ insert (
3. @ Orderheaders as orderitem $ UDT readonly,
4. @ Orderdetails as orderdetail $ UDT readonly)
5. As
6. Begin
7. -- Bulk insert order header rows from TVP
8. Insert into [orderitem]
9. Select *, sysdatetime () from @ orderheaders
10. -- Bulk insert order detail rows from TVP
11. Insert into [orderdetail]
12. Select *, sysdatetime () from @ orderdetails
13. End
14. Go

Copy code

(3) declare a table type variable and reference the table type.

1. ---------------------------
2. If object_id (N 'orderitem', N 'U') is not null
3. Drop table [orderitem]
4. Go
5. Create Table [orderitem] (
6. Orderid int not null primary key,
7. Customerid int not null,
8. Orderedat datetime not null,
9. Createdat datetime2 (0) not null default sysdatetime ()
10. )
11. Go

Copy code

(4) use the insert statement to fill in Table variables.

1. ------------------------
2. Declare @ orderitemudt DBO. orderitem $ UDT
3. Insert into @ orderitemudt
4. Values (, getdate (), (, getdate ())
5. Select * From @ orderitemudt

Copy code

(5) After creating and filling in the table variable, you can pass the variable to the routine.

1. ------------------------
2. Exec DBO. orderitem $ insert @ orderitemudt, @ orderdetailudt
3. Select * From DBO. orderitem

Copy code

2. Advantages

Table value parameters are more flexible and provide better performance in some cases than temporary tables or other methods for passing parameter lists. Table value parameters have the following advantages:

(1) The lock is not obtained when data is filled in from the client for the first time.

(2) provide a simple programming model.

(3) allows a single routine to include complex business logic.

(4) Reduce to round-trips to servers.

(5) Table structures with different base numbers can be created.

(6) is a strong type.

(7) Enable the client to specify the sorting order and unique key.

3. Restrictions

Table value parameters have the following restrictions:

(1) SQL server does not maintain the statistical information of Table value parameter columns.

(2) The table value parameter must be passed to the transact-SQL routine as the input readonly parameter.

DML operations such as update, delete, or insert cannot be performed on Table value parameters in the routine body.

* ** To modify data that has been passed into a stored procedure or a table value type parameter in a parameterized statement, you can only insert data to a temporary table or table variable.

(3) The table value parameter cannot be used as the target of the select into or insert exec statement.

Table value parameters can be in the from clause of select into, or in the insert exec string or stored procedure.

4. Scope

(1) like other parameters, the scope of Table value parameters is stored procedures, functions, or dynamic Transact-SQL text.

(2) Table-type variables have the same scope as any other local variables created using the declare statement.

You can declare Table value variables in dynamic Transact-SQL statements, and pass these variables as Table value parameters to stored procedures and functions.

(3) generally, it is used for data with less than 1000 rows.

It is widely used when the multi-row data of the Browse master is used as a filter condition.

Using TVP makes it easy to insert multiple or select rows at a time. In the past, we used clumsy commas to separate lists or XML files. Although they are competent, they do not exist in the way they are used to, and the access speed is also slow.

For example, when the accounting system selects multiple subjects or multiple departments in multiple departments, the TVP method can greatly improve the access speed and programming readability.

V. Example

1. Use adventureworks
2. Go
3. ------------------------
4. If exists (select * From SYS. types ST join SYS. schemas SS on st. schema_id = ss. schema_id
5. Where St. Name = n' orderitem $ UDT 'and SS. Name = n' dbo ')
6. Drop type [DBO]. [orderitem $ UDT]
7. Go
8. Create type orderitem $ UDT as table (
9. Orderid int primary key,
10. Customerid int,
11. Orderedat datetime)
12. Go
13. ------------------------
14. If exists (select * From SYS. types ST join SYS. schemas SS on st. schema_id = ss. schema_id
15. Where St. Name = n' orderdetail $ UDT 'and SS. Name = n' dbo ')
16. Drop type [DBO]. [orderdetail $ UDT]
17. Go
18. Create type orderdetail $ UDT as table (
19. Orderid int,
20. Linenumber int primary key (orderid, linenumber ),
21. Productid int,
22. Quantity int,
23. Price money)
24. Go
25. ---------------------------
26. If object_id (N 'orderitem', N 'U') is not null
27. Drop table [orderitem]
28. Go
29. Create Table [orderitem] (
30. Orderid int not null primary key,
31. Customerid int not null,
32. Orderedat datetime not null,
33. Createdat datetime2 (0) not null default sysdatetime ()
34. )
35. Go
36. --------------------------
37. If object_id (N 'orderdetail', N 'U') is not null
38. Drop table [orderdetail]
39. Go
40. Create Table [orderdetail] (
41. Orderid int not null,
42. Linenumber int not null primary key (orderid, linenumber ),
43. Productid int not null,
44. Quantity int not null,
45. Price money not null,
46. Createdat datetime2 (0) not null default sysdatetime ())
47. Go
48. ------------------------
49. If object_id (n'orderitem $ insert', n'p') is not null
50. Drop proc orderitem $ insert
51. Go
52. Create procedure orderitem $ insert (
53. @ Orderheaders as orderitem $ UDT readonly,
54. @ Orderdetails as orderdetail $ UDT readonly)
55. As
56. Begin
57. -- Bulk insert order header rows from TVP
58. Insert into [orderitem]
59. Select *, sysdatetime () from @ orderheaders
60. -- Bulk insert order detail rows from TVP
61. Insert into [orderdetail]
62. Select *, sysdatetime () from @ orderdetails
63. End
64. Go
65. ------------------------
66. Declare @ orderitemudt DBO. orderitem $ UDT
67. Insert into @ orderitemudt
68. Values (, getdate (), (, getdate ())
69. Select * From @ orderitemudt
70. ------------------------
71. Declare @ orderdetailudt DBO. orderdetail $ UDT
72. Insert into @ orderdetailudt
73. Values (11,111,111, 12,121,121 1), (13,131,131, 2.12), (, 3.13 ),
74. (21,211,212, 22,222,222 1), (23,231,232, 2.22), (, 3.23 ),
75. (1001.1001, 1, 1002.1002, 1003.1003), (, 2 ),
76. (201.201, 2012.2012)
77. Select * From @ orderdetailudt
78. ------------------------
79. Exec DBO. orderitem $ insert @ orderitemudt, @ orderdetailudt
80. Select * From DBO. orderitem
81. Select * From DBO. orderdetail
82. Go

Copy code