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

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

    本文转自无心之柳.NET博客园博客，原文链接 http://www.cnblogs.com/9527/archive/2005/09/04/230000.html ：，如需转载请自行联系原作者