In this post we are going to learn how to pass a list of values to a SQL Server stored procedure. We will use ADO.NET to call the stored procedure from C#.

We will use an ASP.NET Core 6 application as an example, however, this code should be able to work in any .NET environment that can connect to a database. The only caveat is that the technique we’ll be looking at requires us to use SQL Server 2008 or later. Since we will use Table-Valued Parameters.

A table-valued parameter will allow us to pass a table with data as a parameter to stored procedures or to a function. This table-valued parameter can be defined with multiple columns, in case you need to pass two-dimensional data from one place to another. However, in this post we will simply use a table parameter with one column, but nothing stops you if you need to pass a table with more than one column as a parameter.

Code on github: https://github.com/gavilanch/PassListToStoredProcedure

The problem. We want to solve the following situation, we need to search for a set of records in our database by Id. We want to be able to search for several Ids in a single query. We therefore want to have a stored procedure that can receive the list of Ids so that it returns all the records that we are consulting.

The solution. We will start by executing the following script in our database, which will allow us to create a User-Defined Table Type. This is the table parameter that we are going to pass to the stored procedure:

CREATE TYPE IntegersList as Table (Id int);

As you can see, it’s a type called IntegersList, which contains a single column, called Id, which is of type integer. We will be able to use this type whenever we need to send a list of integers.

After this, we need a table which we are going to consult, in our case it will be an example table:

CREATE TABLE [dbo].[Values](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Value] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Values] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Then, we need the stored procedure which will receive our IntegersList as a parameter:

CREATE PROCEDURE Values_GetValues
	@ListIds IntegersList READONLY
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT *
	FROM [Values]
	WHERE Id IN (SELECT Id FROM @ListIds);
END
GO

As we can see, we receive a parameter named ListIds, whose data type is IntegersList. We are required to define it as readonly. Then, in the query itself, we do a SELECT to our Values table to find the Ids found in our ListIds parameter.

Finally, in our C# application (ASP.NET Core in our case), we need to install the following Nuget package:

Microsoft.Data.SqlClient

Then, we need a class in which we are going to place the data of a record from the Values table:

public class Value
{
    public int Id { get; set; }
    public string? Text { get; set; }
}

Finally, we can use ADO.NET to call the stored procedure and pass it a list of integers using a DataTable (there are other ways to proceed, but to keep the code simple, we’ll use a DataTable):

[HttpGet]
public ActionResult<IEnumerable<Value>> Get([FromQuery] int[] ids)
{
    var result = new List<Value>();
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        using (SqlCommand command = new SqlCommand("Values_GetValues", conn))
        {
            command.CommandType = System.Data.CommandType.StoredProcedure;

            var dt = new DataTable();
            dt.Columns.Add("Id", typeof(int));

            foreach (var id in ids)
            {
                dt.Rows.Add(id);
            }

            var parameter = command.Parameters.AddWithValue("ListIds", dt);
            parameter.SqlDbType = SqlDbType.Structured;

            var reader = command.ExecuteReader();

            while (reader.Read())
            {
                result.Add(new Value()
                {
                    Id = int.Parse(reader["Id"].ToString()!),
                    Text = reader["Value"].ToString()!
                });
            }
        }
    }

    return result;
}

With this we can test sending Ids to the previous method (of course there must be data in the table in question for the test to return data).

Courses

If you want to learn more about .NET or other technologies, please check out my Udemy courses today (discount coupons included): https://www.felipe-gavilan.com/cursos?idioma=eng