Set@PointerCurr=CharIndex(,,@ProductId_Array,@PointerPrev+1)
set@TId=cast(SUBSTRING(@ProductId_Array,@PointerPrev,@PointerCurr-@PointerPrev)asint)
InsertintoProductListSpecial(ModuleId,ProductId)Values(@ModuleId,@TId)
SET@PointerPrev=@PointerCurr
while(@PointerPrev+1<LEN(@ProductId_Array))
Begin
Set@PointerCurr=CharIndex(,,@ProductId_Array,@PointerPrev+1)
if(@PointerCurr>0)
Begin
set@TId=cast(SUBSTRING(@ProductId_Array,@PointerPrev+1,@PointerCurr-@PointerPrev-1)asint)
InsertintoProductListSpecial(ModuleId,ProductId)Values(@ModuleId,@TId)
SET@PointerPrev=@PointerCurr
End
else
Break
End
set@TId=cast(SUBSTRING(@ProductId_Array,@PointerPrev+1,LEN(@ProductId_Array)-@PointerPrev)asint)
InsertintoProductListSpecial(ModuleId,ProductId)Values(@ModuleId,@TId)
SetNoCountOFF
iferror=0
begin
committransaction
end
else
begin
rollbacktransaction
end
GO
网友Bizlogic对此的改善办法:
应当用SQL2000OpenXML更复杂,效力更高,代码更可读:
InsertintoProductListSpecial(ModuleId,ProductId)
Select
@ModuleId,C.[ProductId]
FROM
OPENXML(@idoc,/Products/Product,3)
with(ProductIdint)asC
where
C.[ProductId]isnotnull