SQL Server传送数组参数的变通办法(2)

set @TId=cast(SUBSTRING(@ProductId_Array,@PointerPrev+1,LEN(@ProductId_Array)-@PointerPrev) as int) s%!<CK4
 
Insert into ProductListSpecial (ModuleId,ProductId) Values(@ModuleId,@TId) X'i$/  
Set NoCount OFF ?+gAA ^ D  
if error=0 6T@~o B  
begin X$yvK\hh  
commit transaction u(4a$X.3K  
end MR=/7#&z  
else 8I2,y)JHM  
begin 4U{C& #   
rollback transaction Eu~t5| nh<  
end -, v td{R  
GO "q""X)sT  
>L&)] Bh  
] ,?&def  
网友Bizlogic对此的改进方法: yA$)[dm   
y$" ABWfd  
应该用SQL2000 OpenXML更简单，效率更高，代码更可读： F.}t$L]V.  
.PQ?xa`  
CODE:CREATE Procedure [dbo].[ProductListUpdateSpecialList] $F!KV s%(  
( oh1x=,A<  
@ProductId_Array NVARCHAR(2000), R}5@a{q:_  
@ModuleId INT NoPnC7+  
) b
-C/I>5%  
jx2+)l!iVA  
AS bI {a[e0K  
oVIs(nUv  
delete from ProductListSpecial where ModuleId=@ModuleId ((mD}M  
!l2`:yIU  
-- If empty, return /Wn+V>a  
IF (@ProductId_Array IS NULL OR LEN(LTRIM(RTRIM(@ProductId_Array))) = 0) G/t)2C K  
RETURN +?B [|}gZ  
5pv:  
DECLARE @idoc int 'yOJ1`s(,H  
U`H=k6  
EXEC sp_xml_preparedocument @idoc OUTPUT, @ProductId_Array f[E~@
CD  
,I6EBXMjF  
Insert into ProductListSpecial (ModuleId,ProductId) `gcDQ@R6  
Select |ICJv4g6  
@ModuleId,C.[ProductId] y~Uo\k*d  
FROM s0Im|Ox(  
OPENXML(@idoc, '/Products/Product', 3) * E'p'~<Ln  
with (ProductId int ) as C c{aQ-=i  
where P5^9I#U  
C.[ProductId] is not null uj!Pozz~C  
*B9 M"  
EXEC sp_xml_removedocument @idoc