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

最近一直在做Dnn模块的开发,过程中碰到这么一个问题,需要同时插入N条数据,不想在程序里控制,但是SQL Sever又不支持数组参数.所以只能用变通的办法了.利用SQL Server强大的字符串处理传把数组格式化为类似"1,2,3,4,5,6"。然后在存储过程中用SubString配合CharIndex把分割开来. %!Z;gKs`  
% O
E% _"  
详细的存储过程 xx)=o q';  
B'-p$:=Af  
CODE:CREATE PROCEDURE dbo.ProductListUpdateSpecialList Tw:nvw'P  
@ProductId_Array varChar(800), gP|1O-L  
@ModuleId int V8#Yq@coM  
AS t[6" Af  
DECLARE @PointerPrev int zF43[109  
DECLARE @PointerCurr int #FU6k}a(  
DECLARE @TId int FTwffoc-  
Set @PointerPrev=1 C127IV|.P  
set @PointerCurr=1 'nRkRK/  
@Pt81'T  
begin transaction 8KbIm!#<  
Set NoCount ON eX*3'Wv   
delete from ProductListSpecial where ModuleId=@ModuleId "D0xKE  
t$%(-5  
Set @PointerCurr=CharIndex(',',@ProductId_Array,@PointerPrev+1)
GuTlw  
set @TId=cast(SUBSTRING(@ProductId_Array,@PointerPrev,@PointerCurr-@PointerPrev) as int) ) ~>dhGSM  
Insert into ProductListSpecial (ModuleId,ProductId) Values(@ModuleId,@TId) ;|-Dm   
SET @PointerPrev = @PointerCurr hpjMSn 3  
while (@PointerPrev+1 < LEN(@ProductId_Array)) /sLd(9  
Begin +Gm+Q`x  
Set @PointerCurr=CharIndex(',',@ProductId_Array,@PointerPrev+1) |pf|l-  
if(@PointerCurr> 0) -J cf#  
Begin KRtVI,|G  
set @TId=cast(SUBSTRING(@ProductId_Array,@PointerPrev+1,@PointerCurr-@PointerPrev-1) as int) |em[6A@$9m  
Insert into ProductListSpecial (ModuleId,ProductId) Values(@ModuleId,@TId) R6:RZbZ  
SET @PointerPrev = @PointerCurr 0SST]K;
#A  
End wr>.l G f  
else .] 0>,9f a  
Break LGunR1r;  
End qidBR")u<  
w?-
jI