Table-Valued Parameters in SQL Server

Table-Valued Parameters are like parameter arrays that can be used to send multiple rows of data to a Transact-SQL statement, or a routine such as a stored procedure or a function, avoiding needing to create a temporary table or using many parameters. In this article, I present how to use a Table-Valued parameter in a stored procedure using Microsoft SQL Server.

Table-Valued Parameters are declared by using User-Defined Table Types, which are tables whose purpose is to be used to store temporary data. So for example, if you have a procedure that needs to receive a list of products as a parameter, you can create a Type Table for that and pass it as a parameter.

For demonstration purposes, I created a table of Products, that will be used in the following examples. This is the structure of the Products table:

Inserting a Single Product

Think of a scenario where your user accesses your application and needs to register a single Product in your app. And for that, you need to have a procedure to add this product to the database. In order to do that, you need to create a procedure to add a single product to the Products table. This procedure should receive as parameters the Id, Name, Description and the user who created the product:

For testing this procedure, we can run some scripts adding a BEGIN TRANSACTION with a ROLLBACK in the end (this is useful when testing, to avoid needing to delete/change/revert the data on each test that is made), and inside of that we can execute the statements to insert the products:

This is the result:

Inserting a Bulk of Products

Now think of a scenario where instead of adding a single product, you will receive a list of products to be added to the Products table. In this case, the procedure should contain a Table Type as a parameter (which will work as a kind of an array of products).

The Type Table should contain as columns, the same properties that will be included in the Products table, in this example, the type table will have the Id, Name and Description:

Once the Type Table is created, it’s possible to see it here:

Let’s create then a new procedure named InsertProducts (plural), this procedure will have two parameters: the type table and the user who is adding the records. This is the InsertProducts procedure:

Let’s test the procedure now. For that, let’s use the BEGIN TRANSACTION with a ROLLBACK in the end, as we did before, and for testing we will add some data into the type table and execute the procedure sending this type table as a parameter:

This is the result:

The first SELECT query did not return any data (as expected, since it is a new table). And in the second SELECT query (that was executed after the insert procedure was executed), the three products were added to the Products table.

Let’s make another test for cases when there are records in the Products table. For that let’s insert some data into the table:

Now let’s do another test adding new records using the InsertProducts procedure:

This is the result:

As expected, the new records with Id 4 and 5 were added to the Products table.


When you have a stored procedure or a function that needs to receive as a parameter a list of data, it’s possible to implement it by using Tabled-Valued Parameters, declaring User-Defined Table Types. This way, instead of needing to execute the procedure many times (one time for each data), it’s possible to do a single call sending a bulk of data at once.

This is the link for the repository with the scripts in GitHub:

If you like this solution, I kindly ask you to give an ⭐️ in the repository.

Thanks for reading!



.NET Software Engineer

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store