Jason Magee

Guernsey based independent software consultant, founder of data.gg and aspiring game dev at Granite Games Limited.

Interested in local and remote work.


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.

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)