{"id":1021,"date":"2010-11-03T00:00:00","date_gmt":"2010-11-03T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/using-table-valued-parameters-with-vb-net\/"},"modified":"2021-05-17T18:36:30","modified_gmt":"2021-05-17T18:36:30","slug":"using-table-valued-parameters-with-vb-net","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/development\/dotnet-development\/using-table-valued-parameters-with-vb-net\/","title":{"rendered":"Using Table Valued Parameters with VB.NET"},"content":{"rendered":"
\n

Microsoft has finally solved the problem of how to send multiple rows of data to a database without having to do it one row at a time. No more looking around for inefficient workarounds or using time consuming methods to meet requirements; Table Value Parameters, or TVPs as they are commonly known, have come to the rescue! In layman’s terms, you can now send a whole bunch of rows in one go.<\/p>\n

Why was there a problem? If you are forced to send multiple rows of data from a client application to SQL Server by doing several trips to and from the client application, then your application’s performance was likely to suffer and you wouldn’t be able to easily wrap it all in a single transaction. Sometimes you need to be sure that all alterations to the database are done in one transaction so that, if there is an error, the operation can be entirely rolled back rather than leave the data within the database in an inconsistent state.<\/p>\n

Before Table-Valued Parameters were introduced in SQL Server 2008, programmers had to pass multi-row data as serialized, delimited, strings, long parameter lists, or XML. This made both the application and the server more difficult to program reliably. Erland covers all these painful techniques here: Arrays and Lists in SQL Server 2005 and Beyond: When TVPs Do Not Cut it<\/a><\/p>\n

So what are Table Value Parameters? Table-valued parameters provide an easy way to send. You simply pass a table value parameter through a stored procedure to the server. This feature is available in SQL server 2008 only. Earlier versions of SQL Server do not have this functionality.<\/p>\n

How does it work?<\/h1>\n

Although this recent feature sounds useful, a number of steps to be taken first before you use it. Table-Valued Parameters are declared using user-defined table types. It is not as complicated as it sounds. Firstly, we need to create a table type. Secondly, we create a variable of the table type that we just created and populate it. The variable will actually contain the structure of a database table. Once the table is populated with data, we can manipulate it as we would any other table, until it gets passed as a parameter.<\/p>\n

Let’s put this in practice, first let’s create the database and the table.<\/p>\n

Our sample database is just going to contain the names of our contacts just do demonstrate the use of TVPs. The code will first create a database and then a table. SQL Server provides SQL Management Studio (SSMS) that makes it simple to create databases and tables by using the Object Browser. I will show both ways of creating a table type for our next step. Firstly, I’ll use SQL to create both the database and table. In SSMS add this query and execute it:<\/p>\n

USE master  Go\r\nCREATE DATABASE contacts\r\ngo\r\nUSE contacts\r\nGO--create table\r\nCREATE TABLE contact (\r\ncontactID INT PRIMARY KEY,\r\nfname VARCHAR(30),\r\nlname VARCHAR(30))\r\nGO\r\n<\/pre>\n
Once your database and table is created, then the next thing we do is to create the table type.<\/p>\n
-- ================================\r\n-- Create User-defined Table Type\r\n-- ================================\r\nUSE Contacts\r\nGO\r\n\r\n-- Create the data type\r\nCREATE TYPE dbo.contactType AS TABLE \r\n(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ContactID INT NOT NULL, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 fName VARCHAR(30) NOT NULL, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 lname VARCHAR(30) NOT NULL, \r\n\u00a0\u00a0\u00a0 PRIMARY KEY (ContactID)\r\n)\r\nGO\r\n<\/pre>\n
You’ll see that this syntax is pretty close to the CREATE TABLE one. You can define PRIMARY KEY, UNIQUE but not CHECK constraints, you can use IDENTITY and DEFAULT definitions, and you can define computed columns, but there are restrictions. \u00a0You cannot use ALTER TABLE statements to change the design of table-valued parameters<\/p>\n
If you prefer, you can expand the database then select and expand the Programmability<\/b> option. Do the same with the Types<\/b> option and find the User-Defined Table Types<\/b> options:<\/p>\n
\"1171-L1.jpg\"<\/p>\n
Right click on it and select the ‘New User Defined Table Type’<\/b> option. The following code should appear in the query window:<\/p>\n
-- ================================  -- Create User-defined Table Type\r\n-- ================================\r\nUSE <database_name,sysname,AdventureWorks>\r\nGO\r\n\r\n-- Create the data type\r\nCREATE TYPE <schema_name,sysname,dbo>.<type_name,sysname,TVP> AS TABLE \r\n(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 <columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 <column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 <column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>, \r\n\u00a0\u00a0\u00a0 PRIMARY KEY (<columns_in_primary_key, , c1>)\r\n)\r\nGO\r\n<\/pre>\n
This is a template. You need just hit Control Shift M and fill in the form that is then shown. This will generate the same code as above:<\/p>\n
After you’ve executed the code, you can verify that the data type has been created by checking in the object Explorer:<\/p>\n
\"1171-L2.jpg\"<\/p>\n
Now that we have this table type defined, we have to make it useable. For example if we want to insert data or use any other T-SQL, we have to create a variable of the new table type and populate it. For those familiar with Object Oriented Programming, this is a bit like instantiating a class or object. Once this new variable is created it will inherit the table structure of the new type. This is how it is done:<\/p>\n
First of all we declare a variable of table type:<\/p>\n
DECLARE @Mtabletype contactType  \r\n\/* The variable name @Mtabletype can be called anything you want but the table type has to reference an existing table definition type. Now we insert the data: *\/\r\n\r\nINSERT\u00a0 INTO @Mtabletype (contactID, fname, lname)\r\nVALUES\u00a0 (1, 'Dantago', '!Noabes'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (2, 'Hadago', '!Nuwuseb'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (3, '!Garibasen', 'Siebeb'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (4, 'Mubasen', '\/\/Gaseb'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (5, 'Amase', '\/\/Noabes'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (6, 'Axaro', 'Xamiseb'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (7, 'Xoagu', '\/\/Gowases'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (8, 'Tia', 'Doeses'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (9, 'Kamro', 'Araes') ;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\/* Once the table is populated, we can manipulate the data like any other table, provided the variable is in scope: *\/\r\n\r\nSELECT * FROM @Mtabletype\r\n<\/pre>\n
Here’s the result when you run the above code:<\/p>\n
\"1171-L3.jpg\"<\/p>\n
The next step is to declare a stored procedure that has a parameter of our new table type that we have defined. We’ll then pass our variable as a parameter to a stored procedure. We’ve already got a regular table that we can populate; the one we created at the beginning, when we created the database. All we need now is to create a stored procedure that will populate that table:<\/p>\n
CREATE PROC insertTVP\r\n\u00a0 @MParam contactType READONLY\r\nAS \r\nINSERT\u00a0 INTO dcontacts (contactID, fname, lname)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 contactID, fname, lname\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 @MParam\r\nGo \r\n<\/pre>\n
So what does the code above do? Well, first of all, we define the stored procedure by adding our table type contactType then we add a READONLY<\/span> qualifier. This qualifier is very important and MUST be included when a stored procedure is created. This is mainly because Transact-SQL passes table-valued parameters to routines by reference. Basically, a pointer is passed to the stored procedure and not as we would have expected, the user defined table variable. It therefore isn’t possible to do DML operations that update a table-valued parameter, such as UPDATE, DELETE, or INSERT within a routine. Table-valued parameters are read-only in Transact-SQL code. If you need to modify the data that is passed to a stored procedure or parameterized statement in a table-valued parameter, you will need to insert the data into a temporary table or into a table variable first. It isn’t actually much of a problem to do this, and takes little time.<\/p>\n
Now that we have created the code for the stored procedure we can run it. After running it, you should be able to see it in the project explorer as below:<\/p>\n
\"1171-L4.jpg\"<\/p>\n
Now all that remains is for us to pass our variable to the stored procedure. It will have to be a stored procedure rather than a function since MSDN says that you cannot pass table-valued parameters to a user-defined function and, anyway, you wouldn’t be able to do an INSERT into a table from within it since DML statements to tables are invalid in scalar UDFs. Lets populate the contact table that we created earlier, add the following code to a query window in SSMS and execute it:<\/p>\n
DECLARE @Mtabletype contactType  DELETE FROM contact\r\nINSERT\u00a0 INTO @Mtabletype (contactID, fname, lname)\r\nVALUES\u00a0 (1, 'Dantago', '!Noabes'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (2, 'Hadago', '!Nuwuseb'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (3, '!Garibasen', 'Siebeb'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (4, 'Mubasen', '\/\/Gaseb'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (5, 'Amase', '\/\/Noabes'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (6, 'Axaro', 'Xamiseb'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (7, 'Xoagu', '\/\/Gowases'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (8, 'Tia', 'Doeses'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (9, 'Kamro', 'Araes') ;\r\n\r\nEXEC inserttvp @MParam = @Mtabletype\r\nSELECT * FROM contact\r\ngo\r\n<\/pre>\n
A large part of the code above should be familiar to you. We’ve just added the code to execute the stored procedure.<\/p>\n
At which point did we pass the table as a parameter? First we declare a variable of our table type:<\/p>\n
DECLARE @Mtabletype contactType <\/pre>\n
At this point our contacts are stored in the @Mtabletype variable. Then we simply pass that variable to the stored procedure that we created:<\/p>\n
EXEC inserttvp @MParam = @contact\r\n<\/pre>\n
When I run the query on the base table…<\/p>\n
SELECT * FROM contact<\/pre>\n
…I get exactly the same result as from the TVP. The physical table is now populated with the data from the TVP! We have demonstrated that it is possible to use a Table-Valued Parameter to send multiple rows of data to a Transact-SQL statement or\u00a0 a stored procedure \u00a0without creating a temporary table, pass an XML variable, \u00a0or having the give the procedure a whole lot of parameters.<\/p>\n
Why is this useful? It is because we can pass tables from the client application and add them, via a Stored Procedure, to the database, in just one round trip. This is far quicker than any other way of passing multiple rows to the database. We’ll move on to show how this is done, using a simple illustration in VB NET.<\/p>\n
Creating a VB.NET Client Application<\/h1>\n
So far we’ve been using the SQL 2008 SSMS as if it were a client that was interacting with the database. Now we’ll illustrate the same technique from application code. We will be creating a client application using Visual Studio 2008 VB.NET. To start with, make sure that you have dot net framework 3.5 installed. Remember how we created a temporary table to hold our data earlier in the article? That is exactly what we are going to do in our application. Then we will send that data straight to our ‘contacts’ database. First create a windows application and add a button. We will be using stored procedure that we created earlier, so we don’t need an output control on our form. Just a button will do. Double click on the button and add the following code:<\/p>\n
Imports System.Data.SQLClient<\/pre>\n
In this code, I’ve imported the SQLclient name space. This namespace contains a data type that we need in order to use Table-Valued Parameters. Next we create the temporary table that is going to hold our data. I’ve commented the code very heavily so that it is self explanatory:<\/p>\n
Public Class Form1\r\n\r\n\u00a0\u00a0\u00a0 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ''create table\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim Table1 As DataTable\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0'create a table named tmptbl\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Table1 = New DataTable(\"tmptbl\")\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim Row1, Row2, Row3 As DataRow\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'declaring three rows for the table\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Try\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'declare a column named contactID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim contactID As DataColumn = New DataColumn(\"contactID\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'setting the datatype for the column\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 contactID.DataType = System.Type.GetType(\"System.String\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'adding the column to table\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Table1.Columns.Add(contactID)\r\n\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'repear process for the remaining two columns\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim fname As DataColumn = New DataColumn(\"fname\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 fname.DataType = System.Type.GetType(\"System.String\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Table1.Columns.Add(fname)\r\n\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim lname As DataColumn = New DataColumn(\"lname\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 lname.DataType = System.Type.GetType(\"System.String\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Table1.Columns.Add(lname)\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'declaring a new row\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Row1 = Table1.NewRow()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'filling the row with values. Item property is used to set the field value.\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'filling the row with values. adding a contactID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Row1.Item(\"contactID\") = \"10\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'filling the row with values. adding a lname\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Row1.Item(\"fname\") = \"Dantago\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'filling the row with values. adding a fname\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Row1.Item(\"lname\") = \"!Noabes\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'adding the completed row to the table\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Table1.Rows.Add(Row1)\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Repeat the process for all rows you want to add\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Row2 = Table1.NewRow()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Row2.Item(\"contactID\") = \"11\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Row2.Item(\"fname\") = \"Xoagu\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Row2.Item(\"lname\") = \"\/\/Gowases\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Table1.Rows.Add(Row2)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Row3 = Table1.NewRow()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Row3.Item(\"contactID\") = \"12\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Row3.Item(\"fname\") = \"Kamro\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Row3.Item(\"lname\") = \"Araes\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Table1.Rows.Add(Row3)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Catch\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End Try\r\n<\/pre>\n
We then make a connection to the database:<\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'establishing connection. you need to provide password for SQL server\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim myConnection = New SQLConnection()\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 myConnection.ConnectionString = \"Your connection string here\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Try\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 myConnection.Open()\r\n<\/pre>\n
Next we set the stored procedure that we will be using and add the parameter that we used earlier:<\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim myCommand As New SQLCommand(\"inserttvp\", myConnection)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim myparam As SQLParameter = myCommand.Parameters.Add(\"@Mparam\", SQLDbType.Structured)\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'create parameter\r\n\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0myCommand.CommandType = CommandType.StoredProcedure<\/pre>\n
Then we run the stored procedure after adding the table name:<\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 myparam.Value = table1\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 myCommand.ExecuteNonQuery()\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Catch ex As SQLException\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MsgBox(ex.Message)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End Try\r\n\u00a0\u00a0\u00a0 End Sub\r\nEnd Class<\/pre>\n
To use TVPs in this way, you have to use System.Data.SqlClient. We’ve show it used with a DataTable, but you can use the technique from a list that is instantiated from the SqlDataRecord class, or from a DbDataReader<\/a>. As far as I know, none of the major ORMs support this technique, not even Entity Framework. The ODBC driver for SQL Server Native Client allows you to use it but it is best to check out the rather more complex code here Readme_Table-Valued Parameters (ODBC).<\/a><\/p>\n
Conclusion<\/h1>\n
Using table valued parameters enables database applications to perform much better than they otherwise would have, by reducing the amount of trips the application makes to the server. You can now just send multiple rows to the server with one call. An OLTP transaction that involves many rows of data can be contained within a single stored procedure call. Without table-valued parameters, you would need to hold the transaction open whilst several calls are made to the server. TVPs are far simpler to process than delimited lists, and there is better type-checking. It is faster, and more efficient than using XML variables to do the same thing.<\/p>\n
It isn’t necessarily a panacea in all cases. It certainly makes it simple to pass whole tables of strongly-typed data to the application in one call, where it can be absorbed easily in one transaction without causing the generation of a lot of locks. However, there are restrictions: the data is read-only and, because statistics aren’t generated on TVPs, could be slow to process within the receiving routine if there is a lot of complexity involved. On balance, though, it might very well work for you.<\/p>\n
Further reading<\/h2>\n