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

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
        insert into MyTable (Name, Value)
        select Name, Value from @items

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))
    using (var cmd = new SqlCommand("MyTableAddMultipleItems", con)
        CommandType = CommandType.StoredProcedure
        //Creating the table parameter
        var itemsTable = new DataTable();
        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);

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s