SQL Server 2008 引入「表值參數」,讓開發者能直接將資料表結構資料傳遞給預存程序,徹底解決舊版需用 XML、逗號分隔字串或臨時表的痛點。
CREATE TYPE [dbo].[UsTable] AS TABLE (
[id] INT IDENTITY(1,1) NOT NULL,
[NAME] VARCHAR(50) NULL
);
CREATE PROCEDURE [dbo].[AddUsers]
@UserList [dbo].[UsTable] READONLY -- 必須指定 READONLY
AS
BEGIN
INSERT INTO [dbo].[Users] (UserName, Password)
SELECT NAME, '123' FROM @UserList; -- 批次插入,預設密碼 123
END;
-- 宣告表變數
DECLARE @UserBatch [dbo].[UsTable];
-- 填入測試資料
INSERT INTO @UserBatch (NAME) VALUES ('Alice');
INSERT INTO @UserBatch (NAME) VALUES ('Bob');
INSERT INTO @UserBatch (NAME) VALUES ('Charlie');
-- 執行預存程序
EXEC [dbo].[AddUsers] @UserList = @UserBatch;
| 特性 | 傳統方案 | 表值參數 |
|---|---|---|
| 型別安全 | 無(字串/XML) | 強型別,編譯期檢查 |
| 效能 | 解析開銷大 | 直接資料流,無序列化 |
| 程式碼複雜度 | 高(組裝/剖析) | 低(原生集合操作) |
| 統計資料 | 無 | 優化器可參考資料分佈 |
| 並行處理 | 困難 | 支援 |
var dt = new DataTable();
dt.Columns.Add("NAME", typeof(string));
dt.Rows.Add("Alice");
dt.Rows.Add("Bob");
using (var cmd = new SqlCommand("AddUsers", conn) { CommandType = CommandType.StoredProcedure })
{
var param = cmd.Parameters.AddWithValue("@UserList", dt);
param.SqlDbType = SqlDbType.Structured;
param.TypeName = "dbo.UsTable";
cmd.ExecuteNonQuery();
}
OUTPUT 方向,需改用輸出參數或結果集回傳SqlBulkCopy表值參數是 SQL Server 2008+ 開發者的利器,讓集合導向思維貫穿資料庫與應用層,告別字串拼接的惡夢。