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();
}
