Passing a datatable to a Stored Procedure in SQL Server 2008

Table-valued parameters are a new parameter type in SQL Server 2008. 

Table-valued parameters are declared by using user-defined table types.

You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

Of course you can use table-valued parameters in the SQL code.

----------------------------------------

-- create table type

----------------------------------------

CREATE TYPE dbo.TableVariableTest AS TABLE

(

ID int

)

GO

--Note: You can declare a table-valued parameter also with PKs etc.

----------------------------------------

-- create SP that uses the table type

----------------------------------------

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE dbo.SelectTableVariableTestItai

(

    @TableVar dbo.TableVariableTest READONLY

)

AS

BEGIN

    SET NOCOUNT ON;

    

SELECT * 

FROM dbo.Calendar c

JOIN @TableVar v ON v.ID = c.CalID

END

GO

----------------------------------------

-- test:

----------------------------------------

DECLARE @MyTable TableVariableTest

INSERT INTO @MyTable(ID)

VALUES (392),

(477),

(506)

EXEC SelectTableVariableTestItai @TableVar = @MyTable

go

----------------------------------------

-- in the trace it's look like:

----------------------------------------

declare @p1 dbo.TableVariableTest

insert into @p1 values(392)

insert into @p1 values(477)

exec SelectTableVariableTestItai @TableVar=@p1

----------------------------------------

-- the .net code:

----------------------------------------

public DataTable SelectTableVariableTestItai()

{

    DataTable dt = new DataTable();

    dt.Columns.Add("ID", typeof(int));

    DataRow row1 = dt.NewRow();

    DataRow row2 = dt.NewRow();

    row1["ID"] = 392;

    row2["ID"] = 477;

    dt.Rows.Add(row1);

    dt.Rows.Add(row2);

    db.CreateCommand("SelectTableVariableTestItai");

    db.AddParameter("TableVar", dt, SqlDbType.Structured);

    return db.ExecuteDataTable();

}

http://msdn.microsoft.com/en-us/library/bb510489.aspx