This is a short post to demonstrate a simple way to pass an array of data to a stored procedure. I’ve supplied some C# to use it too.
Firstly, create a custom table type in SQL. Note that you only need to do this step once per database.
CREATE TYPE [dbo].[IdList] AS TABLE(
[Id] [int] NULL
)
Update your stored procedure to accept a parameter of the new type.
@Ids AS dbo.IdList READONLY
In C# build the table like this.
var idTable = new DataTable();
idTable.Columns.Add("Id");
foreach (var id in ids) {
idTable.Rows.Add(id);
}
Add it as a command parameter like so…
var tableParam = cmd.Parameters.AddWithValue("@Ids", ids);
tableParam.TypeName = "dbo.IdList";
tableParam.SqlDbType = SqlDbType.Structured;
Finally, use the array in your stored procedure like this.
(a.OtherId IN (SELECT Id FROM @Ids)