【SQL Server】 表值参数(Table-Valued Parameter)的使用

表值参数是使用用户定义的表类型来声明的。 使用表值参数，可以不必创建临时表或许多参数，即可向 Transact-SQL 语句或例程（如存储过程或函数）发送多行数据。

Transact-SQL 通过引用向例程传递表值参数，以避免创建输入数据的副本。 可以使用表值参数创建和执行 Transact-SQL 例程，并且可以使用任何托管语言从 Transact-SQL 代码、托管客户端以及本机客户端调用它们。

在 SQL Server 2008 中，用户定义表类型是指用户所定义的表示表结构定义的类型。您可以使用用户定义表类型为存储过程或函数声明表值参数，或者声明您要在批处理中或在存储过程或函数的主体中使用的表变量。

若要创建用户定义表类型，需要使用 CREATE TYPE 语句定义一个类型。为了确保用户定义表类型的数据满足特定要求，您可以对用户定义表类型创建唯一约束和主键。其实和新建表是一样的语句。

优点

就像其他参数一样，表值参数的作用域也是存储过程、函数或动态 Transact-SQL 文本。 同样，表类型变量也与使用 DECLARE 语句创建的其他任何局部变量一样具有作用域。可以在动态 Transact-SQL 语句内声明表值变量，并且可以将这些变量作为表值参数传递到存储过程和函数。

表值参数具有更高的灵活性，在某些情况下，可比临时表或其他传递参数列表的方法提供更好的性能。 表值参数具有以下优势：

• 首次从客户端填充数据时，不获取锁。

• 提供简单的编程模型。

• 允许在单个例程中包括复杂的业务逻辑。

• 减少到服务器的往返。

• 可以具有不同基数的表结构。

• 是强类型。

• 使客户端可以指定排序顺序和唯一键。

• 在用于存储过程时像临时表一样被缓存。 从 SQL Server 2012 开始，对于参数化查询，表值参数也被缓存。

限制

表值参数有下面的限制：

• SQL Server 不维护表值参数列的统计信息。

• 表值参数必须作为输入 READONLY 参数传递到 函数或存储过程中。 不能在函数或存储过程中对表值参数执行诸如 UPDATE、DELETE 或 INSERT 这样的 DML 操作。

• 不能将表值参数用作 SELECT INTO 或 INSERT EXEC 语句的目标。 表值参数可以在 SELECT INTO 的 FROM 子句中，也可以在 INSERT EXEC 字符串或存储过程中。

私人写的示例：

CREATE TABLE SEC_UserInfo
(
	USER_ID INT NOT NULL,
	Badge VARCHAR(50)
	--.... 
)
CREATE TABLE SEC_Role
(
	ROLE_ID INT NOT NULL
	--...
)
CREATE TABLE SEC_UserRole
(
	UR_ID INT NOT NULL,
	UR_USERID INT ,
	UR_ROLEID INT 
)

--建议：每一个代码块编写完，最后最好加上 GO 表示该段执行完成，这是一个习惯
--创建表值类型ImportTable AS Table
--和建表时是一样的
CREATE TYPE ImportTable AS TABLE
(
	Badge VARCHAR(50) NULL
)
GO

--判断将要新建的存储过程是否存在，存在则删除
IF OBJECT_ID('asp_ImportRoleUsers') IS NOT NULL
	DROP PROC asp_ImportRoleUsers

GO

--新建存储过程
CREATE PROC asp_ImportRoleUsers
(
	@Import ImportTable READONLY,--表值类型做参数，后需加READONLY
	@ROLEID INT 
)
AS

BEGIN
	--从@Import中获取数据，并与用户表关联插进临时表#Import中
	SELECT i.Badge,* INTO #Import FROM
	@Import i LEFT JOIN SEC_UserInfo sui ON i.Badge=sui.Badge
	
	--下面就可以像正常表一样操作从表值参数@Import里面获得的临时表#Import数据了
	--....

END
GO

由于使用这种方式的时候比较紧急，其他使用方式，还没尝试，主要资料来源还是MSDN。

参考文件：http://msdn.microsoft.com/zh-cn/library/bb510489.aspx