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

今天做OA程序用到sql server存储过程,需要传递数组参数,但是sql server本身不支持数组,于是从网上找了一些资料.

方法一、利用SQL Server强大的字符串处理传把数组格式化为类似"1,2,3,4,5,6"
 然后在存储过程中用SubString配合CharIndex把分割开来。

存储过程如下：

 1 CREATE  PROCEDURE  dbo.ProductListUpdateSpecialList
 2      @ProductId_Array   varChar( 800),
 3       @ModuleId  int
 4   AS
 5      DECLARE   @PointerPrev  int
 6       DECLARE  @PointerCurr   int
 7      DECLARE   @TId  int
 8       Set  @PointerPrev = 1 
 9      set   @PointerCurr = 1
10      
11      begin  transaction 
12      Set NoCount   ON
13      delete    from ProductListSpecial  where ModuleId = @ModuleId 
14     
15       Set  @PointerCurr = CharIndex ( ' , ', @ProductId_Array , @PointerPrev + 1)
16       set  @TId = cast ( SUBSTRING( @ProductId_Array, @PointerPrev , @PointerCurr - @PointerPrev)   as  int)
17       Insert  into ProductListSpecial (ModuleId,ProductId)   Values( @ModuleId, @TId)
18       SET  @PointerPrev  =   @PointerCurr
19      while  ( @PointerPrev + 1   <  LEN( @ProductId_Array))
20       Begin
21          Set   @PointerCurr = CharIndex(  ' , ', @ProductId_Array,  @PointerPrev + 1)
22           if( @PointerCurr >  0)
23          Begin
24               set  @TId = cast ( SUBSTRING( @ProductId_Array, @PointerPrev  + 1, @PointerCurr -  @PointerPrev - 1)  as   int)
25               Insert  into ProductListSpecial (ModuleId,ProductId)  Values ( @ModuleId, @TId)
26               SET  @PointerPrev  =   @PointerCurr
27          End 
28          else
29               Break
30      End 
31     
32       set  @TId = cast(  SUBSTRING( @ProductId_Array, @PointerPrev +  1, LEN( @ProductId_Array ) - @PointerPrev)  as   int)
33       Insert  into ProductListSpecial (ModuleId,ProductId)  Values ( @ModuleId, @TId)
34       Set NoCount  OFF
35       if  @@error =  0
36      begin
37           commit  transaction
38       end
39      else 
40      begin
41           rollback  transaction
42       end
43 GO 
44

方法二、应该用SQL2000 OpenXML更简单，效率更高，代码更可读。
存储过程如下：

 1 CREATE  Procedure  [ dbo ]. [ ProductListUpdateSpecialList ] 
 2 ( 
 3 @ProductId_Array  NVARCHAR( 2000), 
 4 @ModuleId  INT 
 5 ) 
 6
 7 AS 
 8
 9 delete  from ProductListSpecial  where ModuleId = @ModuleId 
10
11 --  If empty, return 
12 IF ( @ProductId_Array  IS  NULL  OR  LEN( LTRIM( RTRIM( @ProductId_Array)))  =  0) 
13 RETURN 
14
15 DECLARE  @idoc  int 
16
17 EXEC sp_xml_preparedocument  @idoc OUTPUT,  @ProductId_Array 
18
19 Insert  into ProductListSpecial (ModuleId,ProductId) 
20 Select 
21 @ModuleId,C. [ ProductId ] 
22 FROM 
23 OPENXML( @idoc,  ' /Products/Product ',  3) 
24 with (ProductId  int )  as C 
25 where 
26 C. [ ProductId ]  is  not  null 
27
28 EXEC sp_xml_removedocument  @idoc 
29

现在sql存储过程用的少，这一块还没怎么涉及到，但是这个思路很具有启发性，等后面需要用的时候，再根据这篇文章仔细琢磨一下这块知识。