当前位置: 首页 > 图文教程 > 数据库 > MSSQL > SQL Server 2008:表值参数的创建和使用

MSSQL
用SQL语句实现替换字符串
mssql查找备注(text,ntext)类型字段为空的方法
MSSQL数据类型及长度限制详细说明
SQL Server下几个危险的扩展存储过程
一些SQL Server存储过程参数及例子
sql高级技巧几个有用的Sql语句
SQL语句实现SQL Server 2000及Sql Server 2005日志收缩(批量)
用SQL建立索引的方法步骤
MSsql每天自动备份数据库并每天自动清除log的脚本
mssql无数据库日志文件恢复数据库的方法
SQL Server常用管理命令小结
SQL SERVER性能优化综述(很好的总结,不要错过哦)
sqlserver 游标的简单示例
sqlserver只有MDF文件恢复数据库的方法
在SQL Server启动时自动执行存储过程。
在 SQLSERVER 中快速有条件删除海量数据
阿拉伯数字转大写中文_财务常用sql存储过程
SQL Server存储过程的基础说明
列出SQL Server中具有默认值的所有字段的语句
文本、Excel、Access数据导入SQL Server2000的方法

No. « 9 10 11 12 »

技术文章搜索

关键字

MSSQL 中的 SQL Server 2008:表值参数的创建和使用

出处:互联网   整理: 软晨网（RuanChen.com）   发布: 2009-10-17   浏览: 108 ::
收藏到网摘: n/a

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

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

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

　　1) 创建表类型

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

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

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

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

　　接下来我们使用以下的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版本上进行编写并经过测试的。