SQL Server 2008 表值參數 (TVP) 建立與使用完整教學：從表類型到預存程序/函數傳遞

TVP 兩大優勢

• 無需為初始資料加鎖。
• 不導致陳述式重新編譯。

四步驟建立與使用

1. 建立表類型
2. 建立接受表類型參數的預存程序/函數
3. 建立表變數並插入資料
4. 呼叫程序/函數傳遞表變數

完整範例 (CTP6 測試)

1. 建立測試資料庫與目標表

USE [master];
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TestDB') DROP DATABASE TestDB;
CREATE DATABASE TestDB;
USE [TestDB];
CREATE TABLE [dbo].[TestLocationTable] (
    [Id] [int] NULL, [shortname] [char](3) NULL, [name] [varchar](100) NULL
) ON [PRIMARY];
-- 插入初始資料
INSERT INTO TestLocationTable VALUES (1,'NA1','NewYork'), (2,'NA2','NewYork'), (3,'NA3','NewYork'),
(4,'EU1','London'), (5,'EU2','London'), (6,'AS1','Tokyo'), (7,'AS2','HongKong');

2. 建立表類型

IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id=ss.schema_id WHERE st.name=N'OfficeLocation_Tabetype' AND ss.name=N'dbo')
    DROP TYPE [dbo].[OfficeLocation_Tabetype];
CREATE TYPE [dbo].[OfficeLocation_Tabetype] AS TABLE (
    [Id] [int] NULL, [shortname] [char](3) NULL, [name] [varchar](100) NULL
);

3. 建立預存程序 (僅插入不重複資料)

CREATE PROCEDURE usp_InsertProdLocation
    @TVP OfficeLocation_Tabetype READONLY
AS
    SET NOCOUNT ON;
    INSERT INTO TestLocationTable
    SELECT ID, shortname, name FROM @TVP
    WHERE CONVERT(varchar(10),id)+shortname+name NOT IN (
        SELECT CONVERT(varchar(10),id)+shortname+name FROM TestLocationTable
    );

4. 測試呼叫預存程序

DECLARE @TV OfficeLocation_Tabetype;
INSERT INTO @TV VALUES (12,'ME1','Dubai'), (13,'ME2','Tehran'), (17,'EA1','Bombay'), (18,'EA2','Karachi'),
(3,'NA3','NewYork'), (4,'EU1','London');
EXEC usp_InsertProdLocation @TV;
-- 查詢結果：新增 4 筆 (12,13,17,18)，重複 3,4 未插入
SELECT * FROM TestLocationTable;

5. 建立與呼叫函數

CREATE FUNCTION dbo.myfunction (@TV OfficeLocation_Tabetype READONLY) RETURNS INT
AS BEGIN DECLARE @i INT; SET @i = (SELECT COUNT(*) FROM @TV); RETURN @i; END;

DECLARE @TV OfficeLocation_Tabetype;
INSERT INTO @TV VALUES (12,'ME1','Dubai'), (13,'ME2','Tehran'), (17,'EA1','Bombay'), (18,'EA2','Karachi'),
(3,'NA3','NewYork'), (4,'EU1','London');
SELECT dbo.myfunction(@TV); -- 回傳 6

結語

TVP 以表類型為基礎，支援預存程序與函數參數傳遞，免鎖定、免重編譯，大幅簡化集合資料處理。