Hmmmmmmm.

If I had a pound for every time someone has asked me in the past "What's the best way to pass an array of values into a SQL Server stored procedure" then I'd have at least 4 pounds :-)

I've seen people do it with strings.

I've seen people do it with XML (both before and after SQL Server actually had an XML data type).

And now, with SQL Server 2008 we have table valued parameters.

I created a table type;

create type dbo.peopleTable as table
(
  firstName nvarchar(100),
  lastName nvarchar(100)
)

I created a table;

create table people
(
  id int identity primary key,
  firstName nvarchar(100) not null,
  lastName nvarchar(100) not null
)

I created a simple stored procedure;

create procedure dbo.InsertPeople
(
  @data dbo.peopleTable readonly
)
as
  set nocount on
  insert dbo.people select * from @data
go

I called it from ADO.NET on the client side;

        private void InitializeData()
        {
            table = new DataTable();
            table.Columns.Add("firstName", typeof(string));
            table.Columns.Add("lastName", typeof(string));
            dataGridView1.DataSource = table.DefaultView;
        }

i.e. I bound a DataTable to a DataGridView in Windows Forms and then;

            using (SqlConnection con = new SqlConnection(
                "server=.;database=testdb;integrated security=sspi"))
            {
                con.Open();

                using (SqlCommand com = new SqlCommand(
                    "dbo.InsertPeople", con))
                {
                    com.CommandType = CommandType.StoredProcedure;

                    com.Parameters.AddWithValue("data", table);

                    com.ExecuteNonQuery();
                }
                
                con.Close();
            }

And this left me with a lovely feeling of well-being. Finally, passing multi-value entities into a stored procedure in the way that nature intended :-)