SQL Server 2008表值参数的创建和使用步骤

2024-08-31 00:52:33

字体：大中小

来源：转载

供稿：网友

表值参数(Table-valued parameter)是SQL Server数据库2008的新特性之一，在以往的版本中，我们没有办法把表变量当作一个参数传递给存储过程。但在微软的SQL Server 2008中引入了表值参数这个特性，它可以实现此类功能。

表值参数有两个明显的优点：

1：不需要为初始的数据加锁。

2：它不会导致语句重新编译。

表值参数的创建和使用包括以下步骤：

(1) 创建表类型

(2) 创建一个可将表类型作为参数来接受的存储过程或函数

(3) 创建表变量并插入数据

(4) 调用该存储过程和函数，并将表变量作为参数传递。

下面，我们来一步步分解这个创建和使用的过程。首先，我们用以下的DDL SQL语句来创建一个名为“TestDB”的测试数据库：

USE [master]GOIF EXISTS (SELECT name FROM sys.databases WHERE name = N'TestDB')DROP DATABASE TestDBGOCreate database TestDBgo

下面，使用以下的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.objectsWHERE 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版本上进行编写并已经测试成功。

上一篇：在SQL Server 2005数据库中更改数据架构

下一篇：SQL Server 2005 ntext字段的REPLACE处理

学习交流

如何查找有故障的配件

如何查找有故障的配件...

热门图片

猜你喜欢的新闻

猜你喜欢的关注

新闻热点

最牛同桌！我考了696分我同桌考了703分

2024-06-26 22:28:41

650分！高二女生考入北大：遗憾不能上高三

2024-06-26 22:26:16

男生估分600只考了397 妈妈：高考虽重要，但不代表所有

2024-06-26 22:23:01

唐尚珺回应是否会直播带货：有人出100万想和他合作！

2024-06-25 19:29:23

名校抢人名场面：清华、北大太拼了！

2024-06-25 19:22:14

男生高考语文满分！网友：第一次听说

2024-06-25 19:19:15

疑难解答

图片精选

网友关注