给SQL Server存储过程,传送数组参数的变通办法

 最近一直在做Dnn模块的开发,过程中碰到这么一个问题,需要同时插入N条数据,不想在程序里控制,但是SQL Sever又不支持数组参数.所以只能用变通的办法了.利用SQL Server强大的字符串处理传把数组格式化为类似"1,2,3,4,5,6"
 然后在存储过程中用SubString配合CharIndex把分割开来

详细的存储过程

CREATE   PROCEDURE  dbo.ProductListUpdateSpecialList
     @ProductId_Array   varChar ( 800 ),
     @ModuleId   int
AS
     DECLARE   @PointerPrev   int
     DECLARE   @PointerCurr   int
     DECLARE   @TId   int
     Set   @PointerPrev = 1
     set   @PointerCurr = 1
    
     begin   transaction
     Set  NoCount  ON
     delete    from  ProductListSpecial  where  ModuleId = @ModuleId
    
     Set   @PointerCurr = CharIndex ( ' , ' , @ProductId_Array , @PointerPrev + 1 )
     set   @TId = cast ( SUBSTRING ( @ProductId_Array , @PointerPrev , @PointerCurr - @PointerPrev )  as   int )
     Insert   into  ProductListSpecial (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 )  as   int )
             Insert   into  ProductListSpecial (ModuleId,ProductId)  Values ( @ModuleId , @TId )
             SET   @PointerPrev   =   @PointerCurr
         End
         else
             Break
     End
    
     set   @TId = cast ( SUBSTRING ( @ProductId_Array , @PointerPrev + 1 , LEN ( @ProductId_Array ) - @PointerPrev )  as   int )
     Insert   into  ProductListSpecial (ModuleId,ProductId)  Values ( @ModuleId , @TId )
     Set  NoCount  OFF
     if   @@error = 0
     begin
         commit   transaction
     end
     else
     begin
         rollback   transaction
     end
GO