← Back to Articles
Database November 9, 2022
8 min read

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:

CREATE TABLE Products (
	Id INT NOT NULL,
	Name NVARCHAR(100) NULL,
	Description NVARCHAR(200) NULL,
	CreatedDate DATETIME2 NOT NULL CONSTRAINT [DF_Products_CreatedDate] DEFAULT GETUTCDATE(),
	CreatedBy NVARCHAR(150) NOT NULL,
	CONSTRAINT PK_Product PRIMARY KEY(Id)
 );

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:

CREATE PROCEDURE InsertProduct (  
		@Id INT,  
		@Name NVARCHAR(100),  
		@Description NVARCHAR(200),  
		@User NVARCHAR(150)  
	)  
AS   
BEGIN
	INSERT INTO Products (
		Id,
		Name,
		Description,
		CreatedBy
	) 
	VALUES (	
		  @Id,  
		  @Name,  
		  @Description,  
		  @User
	);
END

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:

BEGIN TRANSACTION

	SELECT * FROM Products;

	EXEC InsertProduct 1, 'Galaxy S22', 'Smartphone Samsung Galaxy S22', 'Henrique';
	EXEC InsertProduct 2, 'iPhone 14 Pro', 'Smartphone Apple iPhone 14 Pro', 'Henrique';
	EXEC InsertProduct 3, 'iPhone 13 Pro', 'Smartphone Apple iPhone 13 Pro', 'Henrique';
	
	SELECT * FROM Products;
	
ROLLBACK
  • On line 1, there is the BEGIN TRANSACTION statement, and this is to allow us to revert the changes at the end of the execution.
  • On line 3, we run a SELECT query to check the data in the Products table.
  • On lines 5 up to 7, we run the InsertProduct procedure to insert the products. Note that in order to insert three products, we needed to execute the procedure three times, once for each product.
  • On line 9, we run a new SELECT query to check the data in the Products table.
  • On line 11, there is the ROLLBACK statement, to revert the changes that were made.

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:

CREATE TYPE ProductType AS TABLE (
	Id INT NOT NULL,
	Name NVARCHAR(100) NULL,
	Description NVARCHAR(200) NULL,
	PRIMARY KEY(Id)
);

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:

CREATE PROCEDURE InsertProducts (  
		@Products ProductType READONLY,
		@User NVARCHAR(150)
	)  
AS   
BEGIN
	INSERT INTO Products (
		Id,
		Name,
		Description,
		CreatedBy
	) 
	SELECT	
		prd.Id,
		prd.Name,
		prd.Description,
		@User
	FROM @Products prd
END
  • On line 2, there is the parameter @Productsof type ProductType, and it must have the READONLY keyword.
  • On line 3, there is the parameter @CreatedBy of type NVARCHAR, which is for saving the name of the user who runs the procedure to insert products. Note: this second parameter is here only to demonstrate that even when a procedure has a type table as a parameter, is still possible to use more parameters of different types — in case you need to get the user who executed the SQL script, you can use the SYSTEM_USER in the SQL Script, instead of receiving the user as a parameter.
  • On lines 7, the INSERT statement begins.
  • On line 13, there is the SELECT query, which will read the data from the table type that was received as a parameter (@Products), and it will use the data to insert in the Products table.

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:

BEGIN TRANSACTION

	SELECT * FROM Products;

	DECLARE @Products ProductType;

	INSERT INTO @Products
	SELECT 1, 'Galaxy S22+', 'Smartphone Samsung Galaxy S22+'
	UNION ALL
	SELECT 2, 'iPhone 14 Pro', 'Smartphone Apple iPhone 14 Pro'
	UNION ALL
	SELECT 3, 'iPhone 13 Pro', 'Smartphone Apple iPhone 13 Pro';

	EXEC InsertProducts @Products, 'Henrique';
	
	SELECT * FROM Products;
	
ROLLBACK
  • n line 1, a new transaction is started.
  • On line 3, we first run a SELECT to check the data we have in the Products table before running the procedure.
  • On line 5, the variable of type ProductTypeis declared.
  • On lines 7 up to 12, three records are inserted into the @Products variable.
  • On line 14, the procedure to insert products is executed and receives as parameters the Type table variable (@Products) and a user ('Henrique').
  • On line 16, a new SELECT in the Products table is executed, and the three records are expected to be inserted into the table.
  • On line 18, a rollback is executed to revert the changes.

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:

INSERT INTO Products (Id, Name, Description, CreatedBy)
	VALUES (1, 'Galaxy S21+', 'Smartphone Samsung Galaxy S21+', 'Henrique'),
	        (2, 'Galaxy S22', 'Smartphone Samsung Galaxy S22', 'Henrique'),
		(3, 'Galaxy S22+', 'Smartphone Samsung Galaxy S22+', 'Henrique');

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

BEGIN TRANSACTION

	SELECT * FROM Products;

	DECLARE @Products ProductType;

	INSERT INTO @Products
	SELECT 4, 'iPhone 13 Pro', 'Smartphone Apple iPhone 13 Pro'
	UNION ALL
	SELECT 5, 'iPhone 14 Pro', 'Smartphone Apple iPhone 14 Pro';
	
	EXEC InsertProducts @Products, 'Henrique';

	SELECT * FROM Products;
	
ROLLBACK
  • On line 3, the first SELECT will return the records that were previously added to the Products table.
  • On line 5, the variable of type ProductType is declared.
  • On lines 7 up to 10, two products are added to the ProductType table, which will be used as a parameter to the procedure.
  • On line 12, the procedure InsertProducts is executed.
  • On line 14, a second SELECT is executed, to return the products.

This is the result:

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

Conclusion

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:

https://github.com/henriquesd/SQLExamples

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

Thanks for reading!

References

Using User-Defined Tables — Microsoft Docs

Use Table-Valued Parameters (Database Engine) — Microsoft Docs