SQL Server 2008数据库中使用表值参数(用户自定义表类型)

在SQL Server 2005或更早的版本中的数据库中，表变量是不能作为存储过程的参数的。当多行数据到SQL Server需要发送多行数据到SQL Server ，开发者要么每次发送一列记录，或想出其他的变通方法，以满足需求。虽然在.net 2.0中提供了个SQLBulkCopy对象能够将多个数据行一次性传送给SQL Server，但是多行数据仍然无法一次性传给存储过程。

SQL Server 2008中的T-SQL功能新增了表值参数。利用这个新增特性，我们可以很方便地通过T-SQL语句，或者通过一个应用程序，将一个表作为参数传给存储过程。

1、用户自定义表类型

当第一次看看新的表值参数，我认为使用此功能有点复杂。有几个步骤。要做的第一件事是定义表型。在Management Studio 2008中的“Programmability”?“Type”节点，您可以看到“User-Defined Table Types(用户自定义表类型)”

创建语法

CREATE TYPE dbo.MyType AS TABLE 

(

col1 int NOT NULL, 

col2 varchar(20) NULL, 

col3 datetime NULL, 

    PRIMARY KEY (col1)

)

2、使用用户自定义的表类型

如果打算在T-SQL代码中使用，您还必须创建一个新类型的变量，然后将具体的表的名称赋值给该变量。一旦赋值后，您可以在其他的T-SQL语句中使用它。因为它是一个变量，在批处理完成后，它也自动失效，结束生命周期。

请注意下面的代码，MyType是我们之前刚刚创建的数据类型

DECLARE @MyTable MyType

INSERT INTO @MyTable(col1,col2,col3)

VALUES (1,'abc','1/1/2000'),

(2,'def','1/1/2001'),

(3,'ghi','1/1/2002'),

(4,'jkl','1/1/2003'),

(5,'mno','1/1/2004')

SELECT * FROM @MyTable

在变量的有效范围内，你可以象操作正常的表一样来操作这个变量，如与另一个表象关联或者将变量中的记录填充到另一个表。对于表变量来说，你无法修改表定义。

正如前面提到的，变量不能超出它的有效的范围。如果T-SQL脚本由多个批处理组成，变量只有在批处理内才能创建并有效使用。

3、使用变量作为参数

到目前为止，我们还没有看到经常表变量无法实现的功能。其好处是能够将变量作为参数传给存储过程。当然一个存储过程必须先建立，使用新的类型作为其中的一个参数。

下面这个例子，通过代码创建一个常规表，并对其填充记录。

CREATE TABLE [dbo].[MyTable] (

[col1] [int] NOT NULL PRIMARY KEY,

[col2] [varchar](20) NULL,

[col3] [datetime] NULL,

[UserID] [varchar] (20) NOT NULL

)

GO

CREATE PROC usp_AddRowsToMyTable

@MyTableParam MyType READONLY,

@UserID varchar(20) AS

iNSERT INTO MyTable([col1],[col2],[col3],[UserID])

SELECT [col1],[col2],[col3],@UserID

FROM @MyTableParam

GO

请注意表值参数后面带了个READONLY参数。这是必需的，不能在例程体中对表值参数执行诸如 UPDATE、DELETE 或 INSERT 这样的 DML 操作。

最后，我们对创建表值变量，对变量进行赋值，并调用存储过程。

DECLARE @MyTable MyType

INSERT INTO @MyTable(col1,col2,col3)

VALUES (1,'abc','1/1/2000'),

(2,'def','1/1/2001'),

(3,'ghi','1/1/2002'),

(4,'jkl','1/1/2003'),

(5,'mno','1/1/2004')

EXEC usp_AddRowsToMyTable @MyTableParam = @MyTable, @UserID = 'Kathi'

SELECT * FROM MyTable

为了让用户使用自定义表类型，执行或控制权限必须是理所当然的。以下是授权命令：

GRANT EXECUTE ON TYPE::dbo.MyType TO TestUser;