Pass a list of values as a parameter to a stored procedure in SQL Server

To pass a list of integer values to a stored procedure in SQL Server using a Table-Valued Parameter, also abbreviated as TVP, is a clean and efficient approach. Here’s a step-by-step tutorial on how to use it with a simple list of integers to pass as one unique parameter to a SQL procedure.

The goal here is to simply pass a list of integers in one unique parameter to a SQL Server stored procedure without having to pass each one in separate hard coded input parameters.

Step 1: Define a table type to store the list of values

Before creating the stored procedure, you need to define a custom table type that represents the structure of the data you’ll pass.

1. Open SQL Server Management Studio (SSMS) and connect to your database.
2. Execute the following SQL to create a new table type:

-- Step 1 : CREATE A TABLE TYPE
CREATE TYPE dbo.IntegerListType AS TABLE 
(
   Number INT
);

Step 2: Create a stored procedure that uses the list of values

Now, create a stored procedure that accepts this table type as a parameter.

• In a new query window, write the following SQL code:

-- Step 2 : CREATE THE STORED PROCEDURE
CREATE PROCEDURE [dbo].[SelectCustomersByIDs]
   @CustomerIDs dbo.IntegerListType READONLY
AS
BEGIN

   SELECT *
   FROM   CUSTOMERS
   WHERE  CustomerID
      IN  (SELECT Number FROM @CustomerIDs)

END

• Replace YourDataTable and YourDataColumn with the names of your actual table and column. This procedure will select rows from YourDataTable where YourDataColumn matches any of the integers in the provided list.

Step 3: Execute the SQL Server procedure with the list as a parameter

To use this stored procedure, you’ll populate a variable of the IntegerListType parameter with the integers you want to pass and then execute the procedure.

In a new query window, execute the following SQL:

-- Step 3 : CALL THE PROCEDURE WITH THE TABLE VARIABLE
DECLARE @MyCustomerIDs IntegerListType;

INSERT INTO @MyCustomerIDs (Number)
VALUES (1), (2), (3);

EXEC [dbo].[SelectCustomersByID]
   @CustomerIDs = @MyCustomerIDs; 

Of course, it is a generic example, so you need to replace (1), (2), (3) with the list of integers you want to query. One very useful piece of code in this case would be how to transform directly the list that comes in a comma separated list into a column.

This script is a variant that allows you to transform a list of values separated values into a column.

DECLARE @MyCustomerIDs IntegerListType;

INSERT INTO @MyCustomerIDs (Number)
SELECT CAST(value AS INT)
FROM   STRING_SPLIT('1;3;5', ';');

-- To view the contents of the table variable
SELECT *
FROM   @MyCustomerIDs;

On the same topic, this detailed tutorial allows you to transform any separated value list into a column.

Of course, before executing the script you need to create the sample table.

DROP TABLE IF EXISTS [dbo].[CUSTOMERS];

CREATE TABLE [dbo].[CUSTOMERS](
	[CustomerID] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](20) NULL,
	[LastName] [nvarchar](20) NULL,
	[AddressText] [nvarchar](100) NULL,
	[CountryCode] [nvarchar](3) NULL,
	[CountryName] [nvarchar](50) NULL
);

INSERT [dbo].[CUSTOMERS] ( [FirstName], [LastName], [AddressText], [CountryCode], [CountryName])
VALUES
  (N'Ana', N'Santos', N'123 Rua Principal, Lisbon', N'PT', N'Portugal'),
  (N'Kenji', N'Takahashi', N'456 Nishi-Dori, Tokyo', N'JP', N'Japan'),
  (N'Amina', N'Khan', N'789 Shahrah-e-Faisal, Karachi', N'PK', N'Pakistan'),
  (N'Igor', N'Smirnov', N'321 Krasnaya Ulitsa, Moscow', N'RU', N'Russia'),
  (N'Sophie', N'Dupont', N'654 Rue de Rivoli, Paris', N'FR', N'France');

SELECT * FROM [dbo].[CUSTOMERS];

Step by step tutorial explained in video

Advantages of Using Table-Valued Parameters

Table-Valued Parameters in SQL Server are highly efficient for large datasets and provide type safety by ensuring correct data types, such as integers. This reduces error risks significantly. Additionally, they enhance security by offering protection against SQL injection, a common risk in dynamically constructed SQL queries, making them a safer and more reliable choice for database operations.

And that’s it for this short tutorial on how to pass a list of integer values to a stored procedure in SQL Server using a table-valued parameter. This method is particularly useful for performing operations on a specific set of data identified by those integers.