sql存储过程循环实现事务

//往一张表中添加数据，获取添加数据生成的ID，再往另一张表中添加多条数据

ALTER PROCEDURE [dbo].[AttendanceCardAndDetail_Add]
 @SchoolID int,
 @AC_Count int,
 @OutTime datetime,
 @OutState smallint,
 @StartCardNumber bigint,
 @EndCardNumber bigint,
 @Remark text
AS
BEGIN
 begin tran
 declare @acid bigint;
 insert into AttendanceCard(SchoolID,AC_Count,OutTime,OutState,StartCardNumber,EndCardNumber,Remark)values
 (@SchoolID,@AC_Count,@OutTime,@OutState,@StartCardNumber,@EndCardNumber,@Remark);
 set @acid=@@IDENTITY;//全局变量
 while @AC_Count>0//相当于循环的次数
  begin
   insert into AttendanceCardDetail(AC_ID,SchoolID,CardNumber,State,DistributionState,Remark)values
   (@acid,@SchoolID,@StartCardNumber+@AC_Count-1,0,0,@Remark)
   set @AC_Count=@AC_Count-1;
  end
  if @@ERROR<>0
  begin commit rollback
  return 0
  end
  else
  begin commit tran
  return 1
 end
END

转载于:https://www.cnblogs.com/apes-monkeys/p/4031187.html