SQL Server 2008 表值參數進階應用（二）：限制條件與自訂資料表類型實作

表值參數的兩大限制

1. 唯讀特性：宣告參數時必須加上 READONLY 子句。預存程序內部無法修改 表值參數的資料內容（不可 INSERT/UPDATE/DELETE），僅能讀取用於查詢、連接、篩選等操作。
2. 僅支援輸入：表值參數不能宣告為 OUTPUT，只能作為 INPUT 參數傳入。若需回傳資料集，請改用結果集、游標或暫存表。

使用自訂資料表類型

建立類型一次，多處重用，避免重複定義結構：

-- 1. 建立使用者定義資料表類型 (僅需執行一次)
CREATE TYPE SalesHistoryTableType AS TABLE
(
    ProductID   INT,
    SaleDate    DATE,
    Quantity    INT,
    UnitPrice   MONEY
);
GO

-- 2. 宣告變數並填入資料
DECLARE @DataTable AS SalesHistoryTableType;

DECLARE @i SMALLINT = 1;
WHILE (@i <= 1000)
BEGIN
    INSERT INTO @DataTable (ProductID, SaleDate, Quantity, UnitPrice)
    VALUES (@i, DATEADD(DAY, -@i, GETDATE()), @i % 10 + 1, RAND() * 100 + 10);
    SET @i = @i + 1;
END

-- 3. 傳遞給預存程序
EXEC usp_ProcessSalesHistory @SalesData = @DataTable;

預存程序接收端定義

CREATE PROCEDURE usp_ProcessSalesHistory
    @SalesData SalesHistoryTableType READONLY  -- 必須加 READONLY
AS
BEGIN
    -- 只能讀取，不可修改 @SalesData
    SELECT ProductID, SUM(Quantity * UnitPrice) AS TotalSales
    FROM @SalesData
    GROUP BY ProductID
    ORDER BY TotalSales DESC;
END

適用場景

• 批次傳遞多筆參數化資料至預存程序（如多筆訂單明細、多筆庫存調整）。
• 替代 XML 序列化/剖析，提升效能與型別安全。
• 搭配 MERGE 陳述式實現高效 UPSERT 批次操作。