给SQL Server传送数组参数的变通办法

核心提示： 应该用SQL2000 Openxml(标准化越来越近了)更简单，效率更高，给SQL Server传送数组参数的变通办法(2)，代码更可读： CREATE Procedure [dbo].[ProductListUpdateSpecialList] ( @ProductId_Array NVARCHAR(2000),

应该用SQL2000 Openxml(标准化越来越近了)更简单，效率更高，代码更可读：

CREATE Procedure [dbo].[ProductListUpdateSpecialList]
(
@ProductId_Array NVARCHAR(2000),
@ModuleId INT
)

AS

delete from ProductListSpecial where ModuleId=@ModuleId

-- If empty, return
IF (@ProductId_Array IS NULL OR LEN(LTRIM(RTRIM(@ProductId_Array))) = 0)
RETURN

DECLARE @idoc int

EXEC sp_xml(标准化越来越近了)_preparedocument @idoc OUTPUT, @ProductId_Array

Insert into ProductListSpecial (ModuleId,ProductId)
Select
@ModuleId,C.[ProductId]
FROM
OPENxml(标准化越来越近了)(@idoc, '/Products/Product', 3)
with (ProductId int ) as C
where
C.[ProductId] is not null

EXEC sp_xml(标准化越来越近了)_removedocument @idoc