Defining/Calling a SP with a Table Type parameter

Let’s take this table “MyTable” as example:

Let’s say that we want to have a SP that allows us to add several items to this table in a single call. Among the many ways that we have to achieve this result, one of the cleaner is to send the new items all together in a Table Type parameter. Here is how to do it.

---

1) Creating a new Table Type parameter under Programmability/Types/User-Defined Table Types:

CREATE TYPE [dbo].[MyTableItemType] AS TABLE(
        [Name] [nvarchar](50) NOT NULL,
        [Value] [nvarchar](50) NOT NULL
)
GO

---

2) Creating a new SP that accepts a parameter of type MyTableItemType and use it to add the items to the table:

CREATE PROCEDURE [dbo].[MyTableAddMultipleItems]
        @items MyTableItemType READONLY
AS
BEGIN
        insert into MyTable (Name, Value)
        select Name, Value from @items
END

---

3) Calling the new SP and passing the Table Type parameter to it:

const string conStr = @"Data Source=.\sqlexpress;Initial Catalog=Test;Integrated Security=True";
using (var con = new SqlConnection(conStr))
{
    con.Open();
    using (var cmd = new SqlCommand("MyTableAddMultipleItems", con)
    {
        CommandType = CommandType.StoredProcedure
    })
    {
        //Creating the table parameter
        var itemsTable = new DataTable();
        itemsTable.Columns.Add("Name");
        itemsTable.Columns.Add("Value");
        itemsTable.Rows.Add(new object[] { "Name 1", "Value 1" });
        itemsTable.Rows.Add(new object[] { "Name 2", "Value 2" });
        itemsTable.Rows.Add(new object[] { "Name 3", "Value 3" });
        itemsTable.Rows.Add(new object[] { "Name 4", "Value 4" });
        itemsTable.Rows.Add(new object[] { "Name 5", "Value 5" });
 
        //Sending the table parameter
        cmd.Parameters.AddWithValue("items", itemsTable);
        cmd.ExecuteNonQuery();
    }
    con.Close();
}