SQL Server 2008 表值參數 (TVP) 完整指南：建立、傳遞與實戰範例

為何需要表值參數

開發者常需將資料集合而非單一值傳遞給預存程序。SQL Server 2000 需使用 OPENXML 處理 VARCHAR/XML，SQL Server 2005 引入 XML 資料類型與 XQuery 但仍需組裝與解析。SQL Server 2008 原生支援表值參數，大幅簡化程式設計，免除 XML 組裝解析，並允許用戶端直接傳遞資料表至資料庫。

建立表值參數步驟

1. 建立範例資料表

IF OBJECT_ID('SalesHistory') > 0 DROP TABLE SalesHistory;
GO
CREATE TABLE [dbo].[SalesHistory] (
    [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Product] [varchar](10) NULL,
    [SaleDate] [datetime] NULL,
    [SalePrice] [money] NULL
)
GO

2. 建立使用者定義表類型

CREATE TYPE SalesHistoryTableType AS TABLE (
    [Product] [varchar](10) NULL,
    [SaleDate] [datetime] NULL,
    [SalePrice] [money] NULL
)
GO

可透過 SELECT * FROM sys.table_types 查詢已建立表類型。

3. 建立接受 TVP 的預存程序

CREATE PROCEDURE usp_InsertBigScreenProducts
    @TableVariable SalesHistoryTableType READONLY
AS
BEGIN
    INSERT INTO SalesHistory (Product, SaleDate, SalePrice)
    SELECT Product, SaleDate, SalePrice
    FROM @TableVariable
    WHERE Product = 'BigScreen'
END
GO

TVP 必須宣告為 READONLY，不可作為 OUTPUT 參數，僅能輸入。

4. 宣告變數、填入資料並呼叫

DECLARE @DataTable AS SalesHistoryTableType
DECLARE @i SMALLINT = 1
WHILE (@i <= 1000)
BEGIN
    INSERT INTO @DataTable (Product, SaleDate, SalePrice)
    VALUES ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))
    INSERT INTO @DataTable (Product, SaleDate, SalePrice)
    VALUES ('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))
    INSERT INTO @DataTable (Product, SaleDate, SalePrice)
    VALUES ('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))
    SET @i = @i + 1
END

EXECUTE usp_InsertBigScreenProducts @TableVariable = @DataTable

注意：TVP 實際儲存於 tempdb，透過參考傳遞，確保大量資料高效處理。

5. 驗證結果

SELECT * FROM SalesHistory

限制與總結

• TVP 唯讀，不可修改參數資料。
• 不可作為 OUTPUT 參數。
• 優點：減少伺服器往返、利用表格約束、擴展資料庫引擎程式功能、提升效能。