SQL Server 2008 表值參數 建立與使用完整範例

表值參數 (Table-Valued Parameter, TVP) 是 SQL Server 2008 新功能，允許將表變數作為參數傳遞給預存程序或函數，舊版本無法直接實現。兩大優點：無需鎖定初始資料、不觸發陳述式重新編譯。

四步驟建立與使用

1. 建立表類型 (CREATE TYPE ... AS TABLE)
2. 建立接受表類型參數的預存程序/函數 (READONLY 關鍵字)
3. 宣告表變數並插入資料
4. 呼叫預存程序/函數並傳遞表變數

完整範例腳本

1. 建立測試資料庫與基礎表

USE master;
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'TestDB') DROP DATABASE TestDB;
CREATE DATABASE TestDB;
GO

USE TestDB;
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[TestLocationTable]') AND type = 'U')
    DROP TABLE [dbo].[TestLocationTable];
GO

CREATE TABLE [dbo].[TestLocationTable] (
    [Id] INT NULL,
    [shortname] CHAR(3) NULL,
    [name] VARCHAR(100) NULL
) ON [PRIMARY];
GO

INSERT INTO TestLocationTable (Id, shortname, Name) VALUES
(1, 'NA1', 'NewYork'), (2, 'NA2', 'NewYork'), (3, 'NA3', 'NewYork'),
(4, 'EU1', 'London'), (5, 'EU2', 'London'), (6, 'AS1', 'Tokyo'), (7, 'AS2', 'HongKong');
GO

2. 建立表類型

IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id
           WHERE st.name = 'OfficeLocation_TableType' AND ss.name = 'dbo')
    DROP TYPE [dbo].[OfficeLocation_TableType];
GO

CREATE TYPE [dbo].[OfficeLocation_TableType] AS TABLE (
    [Id] INT NULL,
    [shortname] CHAR(3) NULL,
    [name] VARCHAR(100) NULL
);
GO

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

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[usp_InsertProdLocation]') AND type IN ('P','PC'))
    DROP PROCEDURE [dbo].[usp_InsertProdLocation];
GO

CREATE PROCEDURE usp_InsertProdLocation
    @TVP OfficeLocation_TableType 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);
GO

4. 呼叫預存程序傳遞表變數

USE TestDB;
DECLARE @TV AS OfficeLocation_TableType;
INSERT INTO @TV (Id, Shortname, Name) VALUES
(12, 'ME1', 'Dubai'), (13, 'ME2', 'Tehran'), (17, 'EA1', 'Bombay'),
(18, 'EA2', 'Karachi'), (3, 'NA3', 'NewYork'), (4, 'EU1', 'London');
EXEC usp_InsertProdLocation @TV;
GO

SELECT * FROM TestLocationTable;
/* 結果：新增 4 筆 (12-18)，跳過重複的 3、4 */

5. 建立並呼叫純量函數

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[myfunction]') AND type IN ('FN','IF','TF','FS','FT'))
    DROP FUNCTION [dbo].[myfunction];
GO

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

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

註：腳本於 SQL Server 2008 CTP6 測試通過。表值參數極大簡化批次資料傳遞邏輯，是 2008 版重要開發利器。