如何在sqlserver2008 中传递表值参数

前言:

sqlserver 2008 将在2008年二月正式发布,为了方便大家提前了解2008的新功能,我翻译了一些国外网站的的技术资料,希望可以帮助大家. 本人翻译水平有限,希望大家看后可以指出错误.

 

我们总是希望可以传递表变量给存储过程，如果变量可以声明，那么表变量就可以被传递了。这里呢我介绍如何传递表变量（以及变量中的数据）给存储过程和函数。

 

为什么要将表做为参数传递？

这么多年来，我们会经常遇到需要将一组值而不是单个值传递给存储过程。在多数其他程序语言中，这样的能力很普通。但TSQL不同，因为数据库是存放数据的地方。

在sqlserver2000中可以使用OPENXML存放数据到一个VARCHAR数据类型中并传递它。在sqlserver2005中简单的利用XML数据类型和XQuery可以实现。但即使这样，在你使用数据前你仍然需要先构造和分裂这些XML数据；在SQLserver2008你可以直接传递一个table数据类型给存储过程和函数。因为不需要考虑构造和分析XML数据，所以它极大地简化了开发的过程。这个功能也允许客户端开发人员像普通参数一样传递数据表给服务器端。

 

如何传递表参数：

先建立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

 

第一步建立要使用的表值参数的table类型；这一步是必须的，只有这样，表结构才可以定义在数据库中，然后再在你的代码中使用它。下面的代码建立一个SalesHistoryTableType表类型定义：

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

如果你想查看你系统中定义的table类型，可以执行下面的查询查看系统目录：

SELECT * FROM sys.table_types

 

我定义一个用来处理表值参数的存储过程。下面的过程处理一个为SalesHistoryTableType类型的表值参数，并将表值中Product列值为“BigScreen”的记录导入到SalesHistory中。

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系统数据库而不是内存中。这样呢在处理大量数据的时候相当有效。所有服务器端传递的表变量参数都是通过引用传递的，一个引用就是指向tempdb中表的指针。

EXECUTE usp_InsertBigScreenProducts
@TableVariable = @DataTable

 

 

需要考虑的：

SQL Server 2008表参数的特点是开发和性能上一个很大的进步。利用这种功能你可以：减少服务往返，使用它扩充程序功能。

有些限制你需要记住，像不可以修改参数中的数据、不能使用变量作为OUTPUT。尽管有这样一些缺点，但当SQL Server 2008发布的时候，我仍然会继续探索表参数的特点。

 

原贴地址：

http://www.builderau.com.au/program/sqlserver/soa/Passing-table-valued-parameters-in-SQL-Server-2008/0,339028455,339282577,00.htm