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

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

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

存储过程如下：

 1CREATE 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
43GO 
44

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

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