在SQL Server2005中实现数组映射(三)-技术开发专区

在SQL Server2005中实现数组映射(三)

作者：IT168 极地圣火编辑：李宁 2008-01-04 00:00

在SQL Server2005中实现数组映射(一)

在SQL Server2005中实现数组映射(二)

【IT168 专稿】

六、使用动态输入进行映射

在本部分的例子中，我们可以考虑使用取代对用户ID的硬编码，而使用从一个用户表中获得用户子集的方式。为了做到这一点，首先建立一个用户表，代码如下：

CREATE TABLE [dbo].[Users](
[IDUser] int IDENTITY(1,1) NOT NULL,
[LastName] varchar(50) NULL,
[FirstName] varchar(50) NULL
)
INSERT INTO dbo.Users VALUES ('Smith', 'A')
INSERT INTO dbo.Users VALUES ('Jones', 'C')
INSERT INTO dbo.Users VALUES ('Smith', 'B')
INSERT INTO dbo.Users VALUES ('Smith', 'D')
INSERT INTO dbo.Users VALUES ('Smithson', 'W')
INSERT INTO dbo.Users VALUES ('Miller', 'R')
INSERT INTO dbo.Users VALUES ('Smith', 'Q')
INSERT INTO dbo.Users VALUES ('Rice', 'O')

下面的代码再次使用了上面建立的Permissions表，不过是从用户表中而不是从一个常量列表中获得的用户：

DECLARE @PERMS VARCHAR(50)
SET @PERMS = '2,3,5'
EXEC SP_map
'INSERT INTO Permissions VALUES(
<MAIN_ARG>, <AUX_ARG>, 1)',
@MainArray='SELECT IDUser from dbo.Users
WHERE LastName LIKE ''Smith%''',
@AuxArray=@PERMS, @Permute=1,
@Verbose=1

要注意的是用户列表仍然通过@MainArray参数传入，但是并不使用常量列表，而是使用了一个SELECT表达式。由于@Permute标志已经打开，SP_map演示了类似'Smith%'的所有组合（有五个），还有三个元素的@AuxArray列表。如图7所示：

七、聚合应该注意的地方

当我们集合结果集时，可以按着下面的方法去做：

确认我们的第一个结果集可以为后面的结果集留有位置。

SP_map的@Accumulate标志是非常有用的 ---- 尤其对于返回小结果集的查询来说。我们需要认识道，无认如何，SP_map不能做没有什么规划的事情。如下面的例子，一个常规的查询返回了所有表名的列表。

EXEC sp_msForEachTable " select '?' "

这个查询产生了很多结果集。我们也许会发现我们自己想利用sp_msForEachTablet提供任何查询。这样SP_map将会降低我们的结果集的简单性。第一步要做的在上面已经讨论过了，就是命名一个匿名的列：

EXEC sp_msForEachTable " select '?' as TableName"

接下来我们需要将查询放到字符串中，因此，我们必须使用双引号：

@StmtTemplate = ' EXEC sp_msForEachTable "

select ''?'' as TableName" '

现在，有一点如果不做说明可能会使我们困惑：如果我们只写下面的代码：

   EXEC SP_map
@StmtTemplate = ' EXEC sp_msForEachTable
" select ''?'' as TableName" ',
@Accumulate = 1

... 我们会得到类似下面的错误（除非我们非常幸运）：

Msg 8152, Level 16, State 14, Line 1

String or binary data would be truncated.

那么这是为什么呢？在上面的代码中通过建立一个临时表重新调用了聚合，它的定义将从第一个结果集推断出来。假设结果集包含一个字符。除非这个结果集正好包含了比其它值都行的字符串，否则字结果集将尝试放一个长的字符串放到这个字段中，因此，就会出现上述的错误。因此，我们必须保证让SP_map知道这个name字段可以容纳多长的字符串，而不考虑在第一个结果集中的值是多长或多短。下面是一种解决方案。如果我们的表有更长的名字，需要使用一个更大的转换值：

EXEC SP_map

@StmtTemplate =

'EXEC sp_msForEachTable "select convert(

varchar(128), ''?'') as Name" ',

@Accumulate = 1

八、确保我们的结果集都有同样的结构

正在使用一个@StmtTemplate的模板，如SELECT * FROM <MAIN_ARG>将仅仅为在参数列表中的每一个表工作。换句话说。我们不能聚合一个包含五列的结果集和一个包含三列的结果集。还有就是聚合工作通过动态地产生一个临时表，并通过第一个结果集的结构推断。每一个子集被合并在这个临时表中。因此，每一列必须和这个临时表的结构兼容。

九、SP_map的存储过程API

到现在为止我们已经看到SP_map可以做什么了，在这一节我们给出一个关于SP_map的参数列表：

SP_map @StmtTemplate, @MainArray, @AuxArray,

@Permute, @Verbose, @Process

表1提供了SP_map的参数详细的描述

参数	数据类型	默认	描述
@StmtTemplate	nvarchar 2048	n-one	表示一个DML表达式（如SELECT、INSERT、DBCC等），这些表达式将被映射成表的集合或是其他的值。@StmtTemplate应该包含占位符<MAIN_ARG>和<AUX_ARG>。
@MainArray	nvarchar 2048	none	@MainArray可以是逗号分割的字符串列表，或是一个可以返回一个单独的结果集的SELECT表达式。每一项最多包含128的字符。
@AuxArray	nvarchar 2048	null	@AuxArray和@MainArray的格式类似。它也可以是由逗号分隔的字符串或是一个返回一个拥有一列的结果集。如果@AuxArray为空，<AUX_ARG>占位符不会在@StmtTemplate中描述
@Permute	bit	0	如果打开这个开关，那么<MAIN_ARG>和<AUX_ARG>的所有组合将被计算。如果关闭这个开关，那么@MainArray和@AuxArray必须有同样的结构。
@Accumulate	bit	0	这个参数为了返回多个结果集而设定，SP_map通过默认值返回一个被分割的结果集。通过设置@Accumulate标志可以代替将多个结果集聚合在单独的结果集中。
@AccumulateFilter	nvarchar 2048	null	当我们将多个结果集聚合在一个单独的结果集中时，这个结果集被存储在一个临时表中。由SP_map产生的语句序列以一个简单的SELECT FROM #temp_table*结尾。
@Process	bit	1	为了查看被产生的SQL页不执行它们，可@Process为0.这是非常有用的。然而，我们必须还设置@Verbose为一个非0的值。否则，不仅不会被执行，而且什么都不会输出。

关注我们