SQL Server 2008新功能──传递表值参数

 

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

　　为什么要传递表值参数?

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

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

　　如何传递表格参数?

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

IF OBJECT_ID('SalesHistory')>0 DROP TABLE SalesHistory; GO CREATE TABLE [dbo].[SalesHistory] ( [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [Product] [varchar](10) NULL, [SaleDate] [datetime] NULL, [SalePrice] [money] NULL ) GO

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

CREATE TYPE SalesHistoryTableType AS TABLE ( [Product] [varchar](10) NULL, [SaleDate] [datetime] NULL, [SalePrice] [money] NULL ) GO

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

SELECT * FROM sys.table_types

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

CREATE PROCEDURE usp_InsertBigScreenProducts 　　( 　　@TableVariable SalesHistoryTableType READONLY 　　) 　　AS 　　BEGIN 　　INSERT INTO SalesHistory 　　( 　　Product, SaleDate, SalePrice 　　) 　　SELECT 　　Product, SaleDate, SalePrice 　　FROM 　　@TableVariable 　　WHERE 　　Product = 'BigScreen' 　　END 　　GO

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

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

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

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

DECLARE @DataTable AS SalesHistoryTableType 　　The following script adds 1,000 records into my @DataTable table variable: 　　DECLARE @i SMALLINT 　　SET @i = 1 　　WHILE (@i <=1000) 　　BEGIN 　　INSERT INTO @DataTable(Product, SaleDate, SalePrice) 　　VALUES ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57)) 　　INSERT INTO @DataTable(Product, SaleDate, SalePrice) 　　VALUES('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13)) 　　INSERT INTO @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中的表格。

EXECUTE usp_InsertBigScreenProducts @TableVariable = @DataTable

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

SELECT * FROM SalesHistory

　　总结：

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