表值參數(shù)(Table-valued parameter)是SQL Server 2008的一個(gè)新特性，在以前的版本中，沒(méi)有辦法把表變量當(dāng)作一個(gè)參數(shù)傳遞給存儲(chǔ)過(guò)程。微軟在SQL Server2008中引入了表值參數(shù)的特性，可以實(shí)現(xiàn)這項(xiàng)功能。

表值參數(shù)有兩大優(yōu)點(diǎn)：一是它不需要為初始的數(shù)據(jù)加鎖，二是它不會(huì)導(dǎo)致語(yǔ)句重新編譯。

表值參數(shù)的創(chuàng)建和使用包括以下步驟：

1) 創(chuàng)建表類(lèi)型

2) 創(chuàng)建一個(gè)可將表類(lèi)型作為參數(shù)來(lái)接受的存儲(chǔ)過(guò)程或函數(shù)

3) 創(chuàng)建表變量并插入數(shù)據(jù)

4) 調(diào)用該存儲(chǔ)過(guò)程和函數(shù)，并將表變量作為參數(shù)傳遞。

下面，我們來(lái)一步步分解這個(gè)創(chuàng)建和使用的過(guò)程。首先，我們用以下的DDL SQL語(yǔ)句來(lái)創(chuàng)建一個(gè)名為“TestDB”的測(cè)試數(shù)據(jù)庫(kù)：

USE [master]

GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TestDB')

DROP DATABASE TestDB

GO

Create database TestDB

go

接下來(lái)我們使用以下的DDL SQL語(yǔ)句來(lái)創(chuàng)建一個(gè)名為T(mén)estLocationTable的表：

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語(yǔ)句將數(shù)據(jù)添加到我們上面創(chuàng)建的表中：

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

下一步，我們要?jiǎng)?chuàng)建一個(gè)和TestLocationTable表具有相似表結(jié)構(gòu)的表類(lèi)型(TABLE TYPE)，語(yǔ)句如下：

　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

緊接著，我們要?jiǎng)?chuàng)建一個(gè)可以將表類(lèi)型作為一個(gè)參數(shù)來(lái)接受的存儲(chǔ)過(guò)程，使用的語(yǔ)句如下：

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

這個(gè)存儲(chǔ)過(guò)程將表變量作為導(dǎo)入值接收，并且只插入TestLocationTable中沒(méi)有的數(shù)據(jù)。現(xiàn)在，我們可以嘗試創(chuàng)建一個(gè)表變量，并執(zhí)行上面創(chuàng)建的存儲(chǔ)過(guò)程usp_InsertProdLocation，語(yǔ)句如下：

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

這時(shí)候，我們可以使用以下的TSQL語(yǔ)句從表TestLocationTable查詢(xún)所有的數(shù)據(jù)：

use TestDB

go

select * from TestLocationTable

go

查詢(xún)結(jié)果如下所示：

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)

從返回的結(jié)果，我們可以看到存儲(chǔ)過(guò)程usp_InsertProdLocation 插入了表變量@TV中和表TestLocationTable所有不匹配的行。

我們還可以將表變量傳遞給一個(gè)函數(shù)。下面我們創(chuàng)建一個(gè)簡(jiǎn)單的函數(shù)，語(yǔ)句如下：

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

現(xiàn)在，我們通過(guò)創(chuàng)建一個(gè)表變量并將該變量作為一個(gè)參數(shù)傳遞給已創(chuàng)建的函數(shù)以調(diào)用該函數(shù)，語(yǔ)句如下：

　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

執(zhí)行結(jié)果如下：

　(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版本上進(jìn)行編寫(xiě)并經(jīng)過(guò)測(cè)試的。

更多信息請(qǐng)查看IT技術(shù)專(zhuān)欄