当前位置: 首页 > 图文教程 > 数据库 > MSSQL > SQL Server 2008：传递表值参数

MSSQL
SQLSERVER 中datetime 和 smalldatetime类型分析说明
数据库清除日志文件(LDF文件过大)
按日期选择最晚日期与最早日期对应的数的差值
sql 时间函数 整理的比较全了
SQL Server全文检索查询浅析
SQL 存储过程基础语法之一
SQL Substring提取部分字符串
用户"sa"登陆失败 SQLServer 错误18456的解决方法
MSSQL 检查所使用的语句是否符合标准
drop,truncate与delete的区别
SQL语句查询数据库中重复记录的个数
SQL 导入导出Excel数据的语句
可以获取客户端的IP地址的sql语句
SQL 根据汉字获取全拼的代码
sqlserver exists,not exists的用法
sqlserver substring函数使用方法小结
sql 判断数据库,表，存储过程等是否存在的代码
做购物车系统时利用到得几个sqlserver 存储过程
Sql2000数据库的备份文件恢复到Sql2005的方法
sql 分组查询问题

No. « 112 113 114 115 »

技术文章搜索

关键字

MSSQL 中的 SQL Server 2008：传递表值参数

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

许多人一直希望能够实现把表格变量传递到存储过程中，如果变量可以被声明，那么它就应该能够被传递。而最新的SQL Server 2008则有这项功能!想知道如何才能把表格变量(包括内含的数据)传递到存储过程和功能中去吗?

为什么要传递表值参数?

用户常常会碰到许多需要把数值容器而非单个数值放到存储过程里的情况。对于大部分的编程语言而言，把容器数据结构传递到例程里或传递出来是很常见而且很必要的功能。TSQL也不例外。

SQL Server 2000通过OPENXML可以实现这个功能，用户可以把数据存储为VARCHAR数据类型然后进行传递。到了SQL Server 2005，随着 XML数据类型以及XQuery的出现，这个功能变得容易一点。但用户仍然需要对XML数据进行组建和粉碎才能够使用它，因此这个功能使用起来并不简单。SQL Server 2008则能够把表值数据类型传递到存储过程和功能中，从而大大地简化了编程的工作，因为程序员无需再花心思去组建和解析XML数据了。该功能还可以让客户方开发员传递客户方数据表格到数据库中。

如何传递表格参数?

以销售为例，首先建立一个 my SalesHistory表格，里面包含了产品销售的信息。写以下脚本就可以在数据库里创建你选择的表格：

建立表值参数第一步是创建确切的表格类型，这一步非常重要，因为这样你就可以在数据库引擎里定义表格的结构，让你可以在需要的时候在过程代码里使用该表格。下面的代码创建 SalesHistoryTableType 表格类型定义：

以下为引用的内容：

　CREATETYPESalesHistoryTableTypeASTABLE
　　(
　　[Product][varchar](10)NULL,
　　[SaleDate][datetime]NULL,
　　[SalePrice][money]NULL
　　)
　　GO

如果想要查看系统里其他类型的表格类型定义，你可以执行下面这个查询命令，查看系统目录：

　　SELECT * FROM sys.table_types

我们需要定义用来处理表值参数的存储过程。下面这个程序能够接受指定SalesHistoryTableType类型的表值参数，并加载到SalesHistory中，表值参数在Product列中的值为“BigScreen”：

以下为引用的内容：

　　CREATEPROCEDUREusp_InsertBigScreenProducts
　　(
　　@TableVariableSalesHistoryTableTypeREADONLY
　　)
　　AS
　　BEGIN
　　INSERTINTOSalesHistory
　　(
　　Product,SaleDate,SalePrice
　　)
　　SELECT
　　Product,SaleDate,SalePrice
　　FROM
　　@TableVariable
　　WHERE
　　Product='BigScreen'
　　END
　　GO

传递的表格变量还可以用做任何其他表格的查询数据。

传递表值参数功能的局限性

在传递表值变量到程序中时必须使用 READONLY从句。表格变量里的数据不能做修改——除了修改你可以把数据用于任何其他的操作。另外，你也不能把表格变量用做OUTPUT参数——只能用做input参数。

使用自己的新表格变量类型

首先，要声明一个变量类型SalesHistoryTableType，不需要再一次定义表格结构，因为在创建这个表格类型的时候已经定义过了。

以下为引用的内容：

　　DECLARE@DataTableASSalesHistoryTableType
　　Thefollowingscriptadds1,000recordsintomy@DataTabletablevariable:
　　DECLARE@iSMALLINT
　　SET@i=1
　　WHILE(@i<=1000)
　　BEGIN
　　INSERTINTO@DataTable(Product,SaleDate,SalePrice)
　　VALUES('Computer',DATEADD(mm,@i,'3/11/1919'),DATEPART(ms,GETDATE())+(@i+57))
　　INSERTINTO@DataTable(Product,SaleDate,SalePrice)
　　VALUES('BigScreen',DATEADD(mm,@i,'3/11/1927'),DATEPART(ms,GETDATE())+(@i+13))
　　INSERTINTO@DataTable(Product,SaleDate,SalePrice)
　　VALUES('PoolTable',DATEADD(mm,@i,'3/11/1908'),DATEPART(ms,GETDATE())+(@i+29))
　　SET@i=@i+1
　　END


只要把数据加载到表格变量里，就可以把结构传递到存储过程中。

注意：当表格变量作为参数传递后，表格会在存储在tempdb系统数据库里，而不是传递整个数据集在内存里。因为这样保证高效处理大批量数据。所有服务器方的表格变量参数传递都是通过使用reference调用tempdb中的表格。

以下为引用的内容：

　　EXECUTEusp_InsertBigScreenProducts
　　@TableVariable=@DataTable

想要查询程序是否和预想效果一样，可以执行以下查询来看记录是否已经插入到 SalesHistory表格中：

以下为引用的内容：

　　SELECT * FROM SalesHistory

总结：

虽然SQL Server 2008的参数传递功能的使用还有一些小小的局限性，比如不能修改参数中的数据和把变量用于output，但是它大大提高了程序性能，它可以减少server往返旅程数、利用表格限制并扩展编程在数据库引擎中的功能。