循环批量更新的的存储过程(属于转载)-优快云博客

本文介绍了一种使用SQL存储过程来批量插入数据的方法，通过动态解析模块ID数组，删除旧记录并插入新记录，实现了高效的数据更新。该过程首先开始事务，然后删除指定用户的现有数据，接着解析输入的模块ID数组，逐个插入新的模块ID，最后根据错误状态决定提交或回滚事务。

View Code

CREATE PROCEDURE  addusermodule
@ModuleId_Array  varChar(2000),
@UserId int
AS
DECLARE  @PointerPrev int
DECLARE @PointerCurr  int
DECLARE  @TId int
Set @PointerPrev=1 
set  @PointerCurr=1

begin transaction 
Set NoCount  ON
delete  from tests where userid=@UserId 

Set @PointerCurr=CharIndex (',',@ModuleId_Array ,@PointerPrev+1)
set @TId=cast (SUBSTRING(@ModuleId_Array,@PointerPrev ,@PointerCurr-@PointerPrev)  as int)
Insert into tests (userid,moduleid)  Values(@UserId,@TId)
SET @PointerPrev =  @PointerCurr
while  (@PointerPrev+1  < LEN(@ModuleId_Array))
Begin
Set  @PointerCurr=CharIndex( ',',@ModuleId_Array, @PointerPrev+1)
if(@PointerCurr> 0)
Begin
set @TId=cast (SUBSTRING(@ModuleId_Array,@PointerPrev +1,@PointerCurr- @PointerPrev-1) as  int)
Insert into tests (userid,moduleid) Values (@UserId,@TId)
SET @PointerPrev =  @PointerCurr
End 
else
Break
End 

set @TId=cast(SUBSTRING(@ModuleId_Array,@PointerPrev+ 1,LEN(@ModuleId_Array )-@PointerPrev) as  int)
Insert into tests (userid,moduleid) Values (@UserId,@TId)

delete from tests  where moduleid =0 
Set NoCount OFF
if @@error= 0
begin
commit transaction
end
else 
begin
rollback transaction
end
GO