T-SQL: Table Valued Parameters - "adios" to the Split function

A very common problem that developers have faced is the ability to pass in arrays to SQL Server and although there are various options available for editions up to SQL2005, the arrival in SQL2008 gave us Table Valued Parameters which allows a much neater way of passing in multiple values.

Here is an example using a simple c# console app to add multiple values to a database table. The key part to note here is that the input parameter needs to be set as READONLY and within the c# code you need to pass in a datatable as the parameter.


-- set up the objects
USE tempdb
GO
-- your table type
CREATE TYPE dbo.string_list_tbltype AS TABLE (nm NVARCHAR(255))
GO
-- your table
CREATE TABLE dbo.tblBeatles (Nm NVARCHAR(255), Dt DATETIME)
GO
-- the insert procedure
CREATE PROCEDURE dbo.AddBeatlesMembers
@Members dbo.string_list_tbltype READONLY
AS
INSERT INTO dbo.tblBeatles
SELECT nm, GETDATE()
FROM @Members
GO


And now the c# code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace Music
{
    class Program
    {
        static void Main(string[] args)
        {
            UseTVP();
        }

        public static void UseTVP()
        {
            DataTable BeatlesTable = new DataTable();
            BeatlesTable.Columns.Add("Name", typeof(string));
            
            // Fill with row
            BeatlesTable.Rows.Add("John");
            BeatlesTable.Rows.Add("Paul");
            BeatlesTable.Rows.Add("Ringo");
            BeatlesTable.Rows.Add("George");

            using (SqlConnection conn = new SqlConnection("Server=MyServer;Database=tempdb;Trusted_Connection=True;"))
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "dbo.AddBeatlesMembers";
                SqlParameter param = cmd.Parameters.AddWithValue("@Members", BeatlesTable);

                conn.Open();
                cmd.ExecuteNonQuery();
            }

        }
    }
}

There is also an MSDN article that explains this in more detail.