Passing a datatable to a Stored Procedure in SQL Server 2008

Create a table:

CREATE TABLE dbo.TestTableVariable
(
    ID        INT IDENTITY(1,1)    primary key,
    Name    VARCHAR(10),
    Addr    VARCHAR(10)
)

Create the table data type:

CREATE TYPE dbo.TableVariable AS TABLE
(
    ID        INT
)

Insert some default data:

INSERT INTO dbo.TestTableVariable VALUES ('aaa', 'addr-aaa')
INSERT INTO dbo.TestTableVariable VALUES ('bbb', 'addr-bbb')
INSERT INTO dbo.TestTableVariable VALUES ('ccc', 'addr-ccc')
INSERT INTO dbo.TestTableVariable VALUES ('ddd', 'addr-ddd')

Create the Stored Procedure that has the table variable as the parameter:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.SelectTestTableVariable
(
    @TableVar dbo.TableVariable READONLY
)
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT ID, Name, Addr FROM dbo.TestTableVariable
    WHERE ID IN (SELECT ID FROM @TableVar)
        
END
GO

Here is the C# code for a console application:

static void Main(string[] args)
{
    DataSet dataset = new DataSet();
    DataTable dataTable = new DataTable();
    dataTable.Columns.Add("ID");
    var dr = dataTable.NewRow();
    dr["ID"] = 1;
    dataTable.Rows.Add(dr);
    using (SqlConnection conn = new SqlConnection(
      @"Data Source=.\SqlExpress;Initial Catalog=Work;Integrated Security=True;"))
    {
        conn.Open();       
        using (SqlCommand cmd = new SqlCommand("[dbo].[SelectTestTableVariable]"))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            cmd.Parameters.AddWithValue("@TableVar", dataTable);
            using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
            {
                adp.Fill(dataset);
            }
        }
    }
}