给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

 

 

#1楼   回复  引用    

2005-09-04 23:19 by

应该用SQL2000 OpenXML更简单，效率更高，代码更可读：

CREATE Procedure [dbo].[ProductListUpdateSpecialList]
(
@ProductId_Array NVARCHAR(2000),
@ModuleId INT
)

AS

delete from ProductListSpecial where ModuleId=@ModuleId

-- If empty, return
IF (@ProductId_Array IS NULL OR LEN(LTRIM(RTRIM(@ProductId_Array))) = 0)
RETURN

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @ProductId_Array

Insert into ProductListSpecial (ModuleId,ProductId)
Select
@ModuleId,C.[ProductId]
FROM
OPENXML(@idoc, '/Products/Product', 3)
with (ProductId int ) as C
where
C.[ProductId] is not null

EXEC sp_xml_removedocument @idoc

#2楼   回复  引用  查看    

2005-09-05 09:09 by zfphere      

用transaction。