SQLServer2008:表值参数的创立和应用

　　表值参数有两大长处：一是它不需要为初始的数据加锁，二是它不会导致语句重新编译。

　　表值参数的创立和应用包含以下步骤：

　　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所有不匹配的行。