SQL Server 2008表值參數的創建和使用步驟

表值參數(Table-valued parameter)是SQL Server數據庫2008的新特性之一，在以往的版本中，我們沒有辦法把表變量當作一個參數傳遞給存儲過程。但在微軟的SQL Server 2008中引入了表值參數這個特性，它可以實現此類功能。 表值參數有兩個明顯的優點： 1：不需要爲初始的數據加鎖。 2：它不會導致語句重新編譯。 表值參數的創建和使用包括以下步驟： (1) 創建表類型 (2) 創建一個可將表類型作爲參數來接受的存儲過程或函數 (3) 創建表變量並插入數據 (4) 調用該存儲過程和函數，並將表變量作爲參數傳遞。 下面，我們來一步步分解這個創建和使用的過程。首先，我們用以下的DDL SQL語句來創建一個名爲「TestDB」的測試數據庫： USE [master] GO IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TestDB') DROP DATABASE TestDB GO Create database TestDB go 下面，使用以下的DDL SQL語句來創建一個名爲TestLocationTable的表： USE [TestDB] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [TestLocationTable]') AND type in (N'U')) DROP TABLE [dbo].[TestLocationTable] GO USE [TestDB] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[TestLocationTable]( [Id] [int] NULL, [shortname] [char](3) NULL, [name] [varchar](100) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO 然後，使用以下的DML SQL語句將數據添加到我們上面創建的表中： USE [TestDB] GO insert into TestLocationTable ( Id, shortname, Name) select 1, 'NA1', 'NewYork' insert into TestLocationTable ( Id, shortname, Name) select 2, 'NA2', 'NewYork' insert into TestLocationTable ( Id, shortname, Name) select 3, 'NA3', 'NewYork' insert into TestLocationTable ( Id, shortname, Name) select 4, 'EU1', 'London' insert into TestLocationTable ( Id, shortname, Name) select 5, 'EU2', 'London' insert into TestLocationTable ( Id, shortname, Name) select 6, 'AS1', 'Tokyo' insert into TestLocationTable ( Id, shortname, Name) select 7, 'AS2', 'HongKong' go 下面，我們需要創建一個和TestLocationTable表具有相似表結構的表類型(TABLE TYPE)，語句如下所示： USE [TestDB] GO 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] GO USE [TestDB] GO CREATE TYPE [dbo].[OfficeLocation_Tabetype] AS TABLE( [Id] [int] NULL, [shortname] [char](3) NULL, [name] [varchar](100) NULL ) GO 接下來，需要創建一個可以將表類型作爲一個參數來接受的存儲過程，使用的語句如下： USE [TestDB] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [usp_InsertProdLocation]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[usp_selectProdLocation] GO 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) GO 此存儲過程將表變量作爲導入值接收，並且只插入TestLocationTable中沒有的數據。現在，大家可以嘗試創建一個表變量，並執行上面創建的存儲過程usp_InsertProdLocation，語句如下所示： use TestDB go DECLARE @TV AS [OfficeLocation_Tabetype] INSERT INTO @TV (Id, Shortname, Name) SELECT 12, 'ME1', 'Dubai' INSERT INTO @TV (Id, Shortname, Name) SELECT 13, 'ME2', 'Tehran' INSERT INTO @TV (Id, Shortname, Name) SELECT 17, 'EA1', 'Bombay' INSERT INTO @TV (Id, Shortname, Name) SELECT 18, 'EA2', 'Karachi' INSERT INTO @TV (Id, Shortname, Name) SELECT 3, 'NA3', 'NewYork' INSERT INTO @TV (Id, Shortname, Name) SELECT 4, 'EU1', 'London' exec usp_InsertProdLocation @TV go 此時，可以使用以下的TSQL語句從表TestLocationTable查詢所有的數據： use TestDB go select * from TestLocationTable go 查詢的結果： Id, shortname, name 1, NA1, NewYork 2, NA2, NewYork 3, NA3, NewYork 4, EU1, London 5, EU2, London 6, AS1, Tokyo 7, AS2, HongKong 12, ME1, Dubai 13, ME2, Tehran 17, EA1, Bombay 18, EA2, Karachi (11 row(s) affected) 從返回的結果看，存儲過程usp_InsertProdLocation 插入了表變量@TV中和表TestLocationTable所有不匹配的行。 另外，我們還可以將表變量傳遞給一個函數。下面創建一個簡單的函數，語句如下所示： USE [TestDB] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myfunction]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[myfunction] GO create function dbo.myfunction (@TV OfficeLocation_Tabetype READONLY) returns int as begin declare @i int set @i=(Select COUNT(*) from @TV) return @i end 現在，大家可以通過創建一個表變量並將該變量作爲一個參數傳遞給已創建的函數以調用該函數，該語句如下所示： USE [TestDB] GO DECLARE @TV AS [OfficeLocation_Tabetype] INSERT INTO @TV (Id, Shortname, Name) SELECT 12,'ME1','Dubai' INSERT INTO @TV (Id, Shortname, Name) SELECT 13,'ME2','Tehran' INSERT INTO @TV (Id, Shortname, Name) SELECT 17,'EA1','Bombay' INSERT INTO @TV (Id, Shortname, Name) SELECT 18,'EA2','Karachi' INSERT INTO @TV (Id, Shortname, Name) SELECT 3,'NA3','NewYork' INSERT INTO @TV (Id, Shortname, Name) SELECT 4,'EU1','London' select dbo.myfunction(@TV) go 執行的結果： (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) ----------- 6 注釋：上文中的參考腳本已在SQL Server 2008 CTP6版本上進行編寫並已經測試成功。