您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 表值參數（TVP）完整實作：預存程序傳遞資料表資料的現代作法

編輯：關於SqlServer

SQL Server 2008 引入**表值參數**，以使用者定義資料表型別（UDTT）為基礎，讓預存程序/函數直接接收多筆資料列，取代 XML/字串拆解、暫存表等繁瑣作法，效能與可維護性大幅提升。

完整實作步驟

1. 建立使用者定義資料表型別 (UDTT)

CREATE TYPE LocationTableType AS TABLE (
    LocationName VARCHAR(50),
    CostRate INT
);
GO

2. 建立接收 TVP 的預存程序

CREATE PROCEDURE dbo.usp_InsertProductionLocation
    @TVP LocationTableType READONLY  -- 必須指定 READONLY
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Production.Location (Name, CostRate, Availability, ModifiedDate)
    SELECT LocationName, CostRate, 0, GETDATE()
    FROM @TVP;
END;
GO

3. 用戶端宣告、填入、呼叫

-- 宣告 TVP 變數
DECLARE @LocationTVP AS LocationTableType;

-- 批次填入資料（可來自查詢、應用程式批次繫結）
INSERT INTO @LocationTVP (LocationName, CostRate)
SELECT Name, 0.00 FROM Person.StateProvince;

-- 呼叫預存程序
EXEC usp_InsertProductionLocation @LocationTVP;
GO

關鍵特性與限制

特性	說明
READONLY	TVP 在程序內唯讀，不可 INSERT/UPDATE/DELETE。需修改請先複製到暫存表/表變數。
無統計資訊	TVP 沒有統計資訊，最佳化器假設 1 筆資料。大量資料建議 `OPTION (RECOMPILE)` 或複製到 #temp 後建立統計資訊。
範圍	僅限目前批次/程序呼叫，不跨 Session。
參數化	ADO.NET 使用 `SqlDbType.Structured` + `DataTable`/`IEnumerable<SqlDataRecord>` 高效傳遞。
效能	避免 XML 序列化/剖析、字串分割，直接集合式操作，延遲極低。

適用場景

批次新增/更新/合併（UPSERT）數百~數萬筆資料。
應用程式收集使用者多筆輸入（訂單明細、多選刪除 ID 清單）一次送入。
替代 OPENJSON、STRING_SPLIT、XML 參數等剖析成本高的作法。

ADO.NET 呼叫範例 (C#)

var dt = new DataTable();
dt.Columns.Add("LocationName", typeof(string));
dt.Columns.Add("CostRate", typeof(int));
foreach (var item in items) dt.Rows.Add(item.Name, item.Rate);

using (var cmd = new SqlCommand("usp_InsertProductionLocation", conn) { CommandType = CommandType.StoredProcedure })
{
    var param = cmd.Parameters.AddWithValue("@TVP", dt);
    param.SqlDbType = SqlDbType.Structured;
    param.TypeName = "dbo.LocationTableType";  // 必須指定 UDTT 名稱
    cmd.ExecuteNonQuery();
}