Passing Table Valued Parameters in SQL Stored Procedure

Now here in this tutorial, I’ll explain how you can pass a table valued parameters variable into a stored procedure parameter and perform required DML operations in sql server with example demo code.

In my previous tutorials, I’d explained how to pass TVP or DataTable as Stored Procedure Parameter in asp.net, exporting gridview data to word excel text and pdf, how to show alert message from code-behind and other more cracking tutorials on SQL Server, Asp.net here.

To achieve this, we need to create SQL Table, Table Type and Store Procedure to perform DML (such as Insert, Update etc) tasks. So, follow the easy steps as below.

Creating SQL Table

Following is the script for creating sql table that we will use in this example to insert student results, so create table by executing following script:

Creating SQL User-Defined Table Type

In order to use table value parameters you have to create a table type. The table type is used to describe the structure of the table value parameter. This is similar to making a strong type. Following is the script for creating sql type table, so create type table by executing following script:

Using TVP in T-SQL

Now that you have a table type you have to declare an instance to use it. The following is a quick example that declares an instance of the table type and populates it with some values. Later we will use C# to create a data table that will populate an instance of a table type.

Using TVP in Stored Procedure

In order to use table value parameters in a stored procedure you have to declare an instance of the table type and specify it as READONLY. It is mandatory for the table value parameter to be READONLY so you cannot modify the data inside of the table type variable inside the stored procedure. Below we will insert the data in the table value parameter into the dbo.Result table.

Now if you want to test above stored procedure, you need to follow same process as we used for TVP in T-SQL in our first example except last statement, Check following example:

Last statement executes the created sp_TVPInsertResult stored procedure and insert data values from @TVP Type Table, that is temporarily stored in memory after completing process it will automatically cleared memory as well as records too. Now you can check dbo.[Result] table to check all the 3 sample records that are inserted at a same time as a bulk insert.

Limitations with Passing Table Valued Parameters

You must use the READONLY clause when passing in the table valued variable into the stored procedure. Data in the table variable cannot be modified OR In other words, you can use the data in the table for any other operation. Also, you cannot use table variables as OUTPUT parameters OR In other words, you can only use table variables as INPUT parameters.

Passing DataTable as Table Valued Parameters – [.Net]

Note that, The DataTable is not the only type that can be used for Table Valued Parameters in .NET but you can also use the DataSet or List types as TVP.