Jason Magee

Developer at Cortex, founder of data.gg and aspiring game dev at Granite Games.

Passing an Array to an SQL Stored Procedure

03 Aug 2018

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.

    [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();
foreach (var id in ids) {

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)