Using table-valued parameters in SQL Server

private static void BuildCollection(IEnumerable inputCollection, IList outputCollection, SqlMetaData[] metaData)
            foreach (var i in inputCollection)
                var rec = new SqlDataRecord(metaData);
                rec.SetValue(0, i);

private static void PrepareDataRecordParameter(SqlCommand command, string paramName, IEnumerable records, string typeName)
            if (records.Any())
                var listParam = new SqlParameter(paramName, SqlDbType.Structured);
                listParam.Direction = ParameterDirection.Input;
                listParam.TypeName = typeName;
                listParam.Value = records;


Use these methods as follow

IEnumerable orders = new List {1, 200, 234, 23}

IList drOrders = new List();

SqlMetaData[] IntegerTable =
    new SqlMetaData("Id", SqlDbType.Int)

BuildCollection(orders, drOrders , intIdTable);

var command = new SqlCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "dbo.CheckOrders";
PrepareDataRecordParameter(command, "@orders", drOrders, "dbo.IntIdTable");

Stored procedure looks like this:

@orders [dbo].[IntegerTable] readonly


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s