为SQL Server传数组参数的变通办法

为SQL Server传数组参数的变通办法2007-06-01最近一直在做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

网友Bizlogic对此的改进方法:

应该用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