--chenjing957翻译整理，转贴请注明出自微软BI开拓者www.windbi.com--原帖地址介绍：我们大多数人都很明确在设计数据库代码（如存储过程，函数，视图等）中需要有可重用和易维护的态度。在我们做大型项目开发的时候这是非常重要的部分。代码的一个普通的特性应该是可被识别和迁移到不同的应用程序平台上仍然能够被调用。和存储过程、视图一样。设计代码并使它易于维护和重用，这样能获得更多的效率以及减少错误。
同时，尽管我们会试图完成某个任务，实际上却因为T-SQL的种种限制而令我们困扰其中。有时候你会感觉到T-SQL并不能真正给予我们足够的自由如同其他应用开发平台那样。在这篇文章中，我将试着提供一些例子，在例子中我将能够实现一个可重用的函数来实现传递表参数。
问题：假设，我正在操作一个库存管理系统。这时有一个事务（销售订单，发票，货物接收，存货调整等）到达，我们需要根据事务来更新存货系统中受到影响的项。我们已经有存储过程来保存和更新每一个事务。这些存储过程需要为当前事务来更新存货系统中所有的项。
请注意，"事务"这个词已经不是数据库里"事务"的概念。他们主要是指应用系统所支持的对存货的一系列操作。
既然存货需要从不同的地方更新，意味着要分开不同的代码到单独的存储过程中。然后新的存储过程需要被来自不同的存货更新操作调用。到现在为止看上去很容易，其实困难的部分就是如何传递这些要被更新的项。
使用表变量可能是一种解决思路。如果我们传递一个表变量，里面包含的有被更新项的列表，问题的复杂性随之降低。但是SQL Server并不允许存储过程中将表变量作为参数传递进去。怎么办？
在这个文章里，我试着在存储过程中以XML格式传递一张表。CALLER能够将一张表（查询结果）转换为XML变量然后传递到存储过程中。CALLEE能够将XML参数恢复为表变量或者直接用XQuery来操作XML变量。
CALLERCALLER能够把表转换成XML变量。数据可以来自一张表或者一段查询。在下面的例子里显示了如何在一个查询结果中创建XML变量。
1 /* 2 Let us first create sample table. 3 */ 4 5 CREATE TABLE [dbo].[OrderDetails](
6 [OrderDetailID] [int]
IDENTITY(1,1)
NOT NULL,
7 [ItemNumber] [varchar](20)
COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
8 [Qty] [int]
NULL 9 )
ON [PRIMARY]
10 11 /* 12 Populate the sample table with values 13 */ 14 INSERT INTO OrderDetails(ItemNumber, Qty)
15 SELECT 'A001', 10
16 UNION SELECT 'A002', 20
17 UNION SELECT 'A003', 30
18 /* 19 The query below returns the results in XML format. 20 */ 21 22 SELECT ItemNumber, Qty
FROM OrderDetails
FOR XML RAW(
'item'), ROOT(
'items')
23 24 /* 25 OUTPUT: 26 27 <items> 28 <item ItemNumber="A001" Qty="10" /> 29 <item ItemNumber="A002" Qty="20" /> 30 <item ItemNumber="A003" Qty="30" /> 31 </items> 32 */在
《Advanced XML Processing - II》里，我展示了一些详细的例子来说明不同的方式按照XML来生成和格式化查询结果。
现在，让我们将查询到的XML值整合到XML变量中。
1 -- Declare the variable 2 DECLARE @x
XML 3 4 -- store the results of the Query to XML variable 5 SET @x = (
SELECT ItemNumber, Qty
FROM OrderDetails
FOR XML RAW(
'item'), ROOT(
'items'), TYPE)
6 7 -- select the values from the XML variable (to make sure that we did it correctly) 8 SELECT 9 x.item.value(
'@ItemNumber[1]',
'VARCHAR(20)')
AS ItemNumber,
10 x.item.value(
'@Qty[1]',
'INT')
AS Qty
11 FROM @x.nodes(
'//items/item')
AS x(item)
这时，我们已经有了XML变量，这样我们可以进入到一个子过程或者函数中。XML变量中包含的值我们希望能够在子过程或函数中被处理或更新。这个子过程也能够把XML变量恢复成表或者直接使用XML里面的值。
CALLEE迄今为止，我们已经看到如何根据查询结果来创建一个XML变量。这个变量能够传递到其他存储过程中，并基于传递传递过来的数据项更新存货数据。最简单的方式创建一个wrapper视图来包含XML变量并用它作为表。
让我们创建另一个试验表Inventory，它将通过XML参数更新信息。下面的代码将创建这个表：
1 CREATE TABLE [dbo].[Inventory](
2 [InventoryID] [int]
IDENTITY(1,1)
NOT NULL,
3 [ItemNumber] [varchar](20)
COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
4 [Stock] [int]
NULL 5 )
ON [PRIMARY]
6 7 INSERT INTO Inventory (ItemNumber, Stock)
8 SELECT 'A001', 0
9 UNION SELECT 'A002', 0
10 UNION SELECT 'A003', 0
下面的代码执行需要CALLEE支持：
1 CREATE PROCEDURE [dbo].[UpdateInventory1]
2 (
3 @x
XML 4 )
5 AS 6 7 SET NOCOUNT ON 8 9 /* 10 The code below creates a wrapper view around the XML variable and updates the 11 "inventory" table with the information. 12 */ 13 14 UPDATE Inventory
SET 15 stock = stock + v.Qty
16 FROM Inventory inv
17 INNER JOIN (
18 SELECT 19 x.item.value(
'@ItemNumber[1]',
'varchar(20)')
AS ItemNumber,
20 x.item.value(
'@Qty[1]',
'INT')
AS Qty
21 FROM @x.nodes(
'//items/item')
AS x(item)
22 ) v
ON (v.ItemNumber = inv.ItemNumber)
23 24 RETURN执行现在执行这个存储过程。运行下面的代码：
1 -- Declare the variable 2 DECLARE @x
XML 3 4 -- store the results of the Query to XML variable 5 SET @x = (
SELECT ItemNumber, Qty
FROM OrderDetails
FOR XML RAW(
'item'), ROOT(
'items'), TYPE)
6 7 -- execute the stored procedure 8 EXECUTE UpdateInventory1 @x
9 10 -- review the results 11 SELECT *
FROM inventory
更新存储过程：在这个实例代码上，在XML上创建了一个wrapper视图。这是最好的例子并且最直观的方法。你可以访问它如同访问普通的表和视图一样。将被关注的复杂的XML处理放到视图中。
下面的例子，阐述了另一种语法，它能够在XML变量中直接立即更新表。
1 CREATE PROCEDURE [dbo].[UpdateInventory2]
2 (
3 @x
XML 4 )
5 AS 6 7 SET NOCOUNT ON 8 9 /* 10 This version of the stored procedure has a slightly enhanced version of the 11 TSQL code. This version updates the table directly from the XML variable, 12 rather than converting the XML data to a view. 13 */ 14 15 UPDATE Inventory
SET 16 stock = stock + x.item.value(
'@Qty[1]',
'INT')
17 FROM Inventory inv
18 INNER JOIN @x.nodes(
'//items/item') x(item)
ON 19 (x.item.value(
'@ItemNumber[1]',
'varchar(20)') = inv.ItemNumber)
20 21 RETURN结论在过去很多年，许多次我都遭遇到需要把表传递到函数或者存储过程的情况，既然SQL Server不允许这种操作，唯一的办法就是绕开它。当然有很多方法都能够解决。而且表面上看也没有什么潜在的危险，我并没有进一步测试是否存在效率问题，至少在我使用过程中，直到今天都没有遇到过任何问题。
