SQL Server 2008 引入,**單次參數化呼叫傳遞多行資料**給預存程序/動態 SQL 的內建機制。基礎是 **用戶定義表類型 (UDTT)**。
CREATE TYPE dbo.PersonInfo AS TABLE (
ID INT PRIMARY KEY,
Name NVARCHAR(50),
Age INT
);
CREATE PROCEDURE usp_InsertPersons
@Persons dbo.PersonInfo READONLY -- 必須 READONLY
AS
BEGIN
INSERT INTO Person (ID, Name, Age)
SELECT ID, Name, Age FROM @Persons;
END
var dt = new DataTable();
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Age", typeof(int));
dt.Rows.Add(1, "Alice", 30);
dt.Rows.Add(2, "Bob", 25);
using (var cmd = new SqlCommand("usp_InsertPersons", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
var p = cmd.Parameters.AddWithValue("@Persons", dt);
p.SqlDbType = SqlDbType.Structured;
p.TypeName = "dbo.PersonInfo"; // 必須指定 UDTT 名稱
cmd.ExecuteNonQuery();
}
支援輸入來源:DataTable、IList、DbDataReader (可流式傳輸 OracleDataReader 等)。
| 限制類別 | 具體限制 |
|---|---|
| 定義限制 | 不可作資料表欄位型別、不可作 UDTT 欄位、無 DEFAULT、建立後不可 ALTER、不可作計算欄參數 |
| 索引限制 | 僅允許主鍵/唯一約束建立的索引 |
| TVP 參數限制 | **唯讀**:不可 UPDATE/INSERT/DELETE 參數內容;需修改請先複製至暫表/表變數 |
| 函數限制 | 不可作 UDF 參數、不可作 UDF 回傳型別 |
| 方案 | 缺點 |
|---|---|
| 多個純量參數 | 參數上限 2100、服務端需手動組裝、維護惡夢 |
| 分隔字串 / XML | 需剖析驗證邏輯、型別安全差、大資料效能差 |
| 多條 SQL 批次提交 | 服務端仍逐條執行、網路來回、無原子性 |
| BCP / SqlBulkCopy | 高效但**無法在伺服器端直接處理**,需落地暫表再處理 |
dbo.Types)SqlBulkCopy 直寫暫表,再用 TVP 處理邏輯READONLY 限制,業務邏輯在 SP 內用暫表/表變數中轉