SQL Server 2008 表值參數完整指南：從自訂表類型到 .NET 應用調用全流程

SQL Server 2005 前無法將表變數作為預存程序參數，多列資料傳遞極不便。SQL Server 2008 引入 表值參數 (Table-Valued Parameters, TVP)，徹底解決此痛點。

1. 建立使用者自訂表類型

SSMS → 可程式性 → 類型 → 使用者自訂表類型 → 右鍵新增。範本產生 CREATE TYPE，手動補充欄位與約束：

CREATE TYPE dbo.MyType AS TABLE
(
    col1 int NOT NULL,
    col2 varchar(20) NULL,
    col3 datetime NULL,
    PRIMARY KEY (col1)
)
GO

建立後可在屬性視窗檢視，不可修改（需刪除重建）。

2. T-SQL 中使用表類型變數

DECLARE @MyTable MyType
INSERT INTO @MyTable(col1,col2,col3)
VALUES (1,'abc','1/1/2000'), (2,'def','1/1/2001'), ...
SELECT * FROM @MyTable

變數生命週期限於批次範圍，批次結束自動銷毀。

3. 作為預存程序參數傳遞

CREATE PROC usp_AddRowsToMyTable
    @MyTableParam MyType READONLY,
    @UserID varchar(20)
AS
INSERT INTO MyTable(col1,col2,col3,UserID)
SELECT col1,col2,col3,@UserID FROM @MyTableParam
GO

• READONLY 強制：程序體內不可對 TVP 執行 INSERT/UPDATE/DELETE。
• 調用端：

DECLARE @MyTable MyType
INSERT INTO @MyTable ... -- 填充資料
EXEC usp_AddRowsToMyTable @MyTableParam = @MyTable, @UserID = 'Kathi'

權限授予

GRANT EXECUTE ON TYPE::dbo.MyType TO TestUser;

4. .NET 3.5+ 應用程式調用

需引用 System.Data.SqlClient，使用 SqlDbType.Structured：

Dim table As New DataTable("temp")
table.Columns.Add("col1", GetType(Integer))
table.Columns.Add("col2", GetType(String))
table.Columns.Add("col3", GetType(DateTime))
' 填充資料列 ...

Dim cmd As New SqlCommand("usp_AddRowsToMyTable", conn)
cmd.CommandType = CommandType.StoredProcedure
Dim param As SqlParameter = cmd.Parameters.Add("@MyTableParam", SqlDbType.Structured)
cmd.Parameters.AddWithValue("@UserID", "Kathi")
param.Value = table
cmd.ExecuteNonQuery()

5. 效能效益

TVP 減少應用與資料庫往返次數，單次呼叫傳遞多列資料，顯著提升批量操作效能，是 SQL Server 2008+ 批量資料處理的標準模式。