最近一直在做Dnn模块的开发过程中碰到这么一个问题需要同时插入N条数据不想在程序里控制但是SQL Sever又不支持数组参数所以只能用变通的办法了利用SQL Server强大的字符串处理传把数组格式化为类似
然后在存储过程中用SubString配合CharIndex把分割开来
详细的存储过程
CREATE PROCEDURE dboProductListUpdateSpecialList
@ProductId_Array varChar()
@ModuleId int
AS
DECLARE @PointerPrev int
DECLARE @PointerCurr int
DECLARE @TId int
Set @PointerPrev=
set @PointerCurr=
begin transaction
Set NoCount ON
delete from ProductListSpecial where ModuleId=@ModuleId
Set @PointerCurr=CharIndex(@ProductId_Array@PointerPrev+)
set @TId=cast(SUBSTRING(@ProductId_Array@PointerPrev@PointerCurr@PointerPrev) as int)
Insert into ProductListSpecial (ModuleIdProductId) Values(@ModuleId@TId)
SET @PointerPrev = @PointerCurr
while (@PointerPrev+ < LEN(@ProductId_Array))
Begin
Set @PointerCurr=CharIndex(@ProductId_Array@PointerPrev+)
if(@PointerCurr>)
Begin
set @TId=cast(SUBSTRING(@ProductId_Array@PointerPrev+@PointerCurr@PointerPrev) as int)
Insert into ProductListSpecial (ModuleIdProductId) Values(@ModuleId@TId)
SET @PointerPrev = @PointerCurr
End
else
Break
End
set @TId=cast(SUBSTRING(@ProductId_Array@PointerPrev+LEN(@ProductId_Array)@PointerPrev) as int)
Insert into ProductListSpecial (ModuleIdProductId) Values(@ModuleId@TId)
Set NoCount OFF
if @@error=
begin
commit transaction
end
else
begin
rollback transaction
end
GO
网友Bizlogic对此的改进方法:
应该用SQL OpenXML更简单效率更高代码更可读
CREATE Procedure [dbo][ProductListUpdateSpecialList]
(
@ProductId_Array NVARCHAR()
@ModuleId INT
)
AS
delete from ProductListSpecial where ModuleId=@ModuleId
If empty return
IF (@ProductId_Array IS NULL OR LEN(LTRIM(RTRIM(@ProductId_Array))) = )
RETURN
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT @ProductId_Array
Insert into ProductListSpecial (ModuleIdProductId)
Select
@ModuleIdC[ProductId]
FROM
OPENXML(@idoc /Products/Product )
with (ProductId int ) as C
where
C[ProductId] is not null
EXEC sp_xml_removedocument @idoc
