萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server 2008 表值參數:告別 XML/字串拼接,優雅傳遞資料集

SQL Server 2008 引入「表值參數」,讓開發者能直接將資料表結構資料傳遞給預存程序,徹底解決舊版需用 XML、逗號分隔字串或臨時表的痛點。

三步驟快速上手

步驟 1:建立使用者定義表類型 (UDTT)

CREATE TYPE [dbo].[UsTable] AS TABLE (
    [id] INT IDENTITY(1,1) NOT NULL,
    [NAME] VARCHAR(50) NULL
);

步驟 2:建立接受 TVP 的預存程序

CREATE PROCEDURE [dbo].[AddUsers]
    @UserList [dbo].[UsTable] READONLY  -- 必須指定 READONLY
AS
BEGIN
    INSERT INTO [dbo].[Users] (UserName, Password)
    SELECT NAME, '123' FROM @UserList;  -- 批次插入,預設密碼 123
END;

步驟 3:呼叫端使用方式

-- 宣告表變數
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)強型別,編譯期檢查
效能解析開銷大直接資料流,無序列化
程式碼複雜度高(組裝/剖析)低(原生集合操作)
統計資料無優化器可參考資料分佈
並行處理困難支援

.NET 端呼叫範例

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();
}

注意事項

  • TVP 僅支援 READONLY,預存程序內無法修改參數內容
  • 不支援 OUTPUT 方向,需改用輸出參數或結果集回傳
  • 大量資料(>10萬列)建議分批或改用 SqlBulkCopy
  • UDTT 屬於資料庫物件,部署需納入版控

表值參數是 SQL Server 2008+ 開發者的利器,讓集合導向思維貫穿資料庫與應用層,告別字串拼接的惡夢。

copyright © 萬盛學電腦網 all rights reserved