请问这个用事物怎么做 如调用此存储过程插入10条记录 如果有一条插入失败那么其他的怎么回滚??create PROCEDURE UP_RoleFunction_ADD @RoleId int, @FunId int AS INSERT INTO [RoleFunction]( [RoleId],[FunID] )VALUES( @RoleId,@FunId ) GO
DECLARE @i INT SET @i=0SET XACT_ABORT ON BEGIN TRANWHILE @i<10 BEGIN EXEC UP_RoleFunction_ADD @i,@i+CAST(RAND()*@i as INT) SET @i=@i+1 ENDCOMMIT TRANGO
SET XACT_ABORT ON BEGIN TRAN EXEC UP_RoleFunction_ADD 1,1 EXEC UP_RoleFunction_ADD 2,2 ... SET @i=@i+1if(@@error=0) COMMIT TRAN else rollback Tran
SET XACT_ABORT ON BEGIN TRAN EXEC UP_RoleFunction_ADD 1,1 EXEC UP_RoleFunction_ADD 2,2 ... SET @i=@i+1if(@@error=0) COMMIT TRAN else rollback Tran
BEGIN TRAN myTranDECLARE @i INT DECLARE @RI INTSET @i=0 WHILE @i<10 BEGIN SET @RI = @i + CAST(RAND()*@i AS INT) EXEC UP_RoleFunction_ADD @i,@RI SET @i = @i+1 ENDIF @@ERROR<>0 BEGIN ROLLBACK TRAN myTran END ELSE BEGIN COMMIT TRAN myTran END
首尾加 BEGIN TRAN 。 END TRAN
begin tran 循环体 if @@error!=0 rollback tran else commit tran
DECLARE @i INT
SET @i=0SET XACT_ABORT ON
BEGIN TRANWHILE @i<10
BEGIN
EXEC UP_RoleFunction_ADD @i,@i+CAST(RAND()*@i as INT)
SET @i=@i+1
ENDCOMMIT TRANGO
BEGIN TRAN
EXEC UP_RoleFunction_ADD 1,1
EXEC UP_RoleFunction_ADD 2,2
...
SET @i=@i+1if(@@error=0)
COMMIT TRAN
else
rollback Tran
BEGIN TRAN
EXEC UP_RoleFunction_ADD 1,1
EXEC UP_RoleFunction_ADD 2,2
...
SET @i=@i+1if(@@error=0)
COMMIT TRAN
else
rollback Tran
BEGIN TRAN myTranDECLARE @i INT
DECLARE @RI INTSET @i=0
WHILE @i<10
BEGIN
SET @RI = @i + CAST(RAND()*@i AS INT)
EXEC UP_RoleFunction_ADD @i,@RI
SET @i = @i+1
ENDIF @@ERROR<>0
BEGIN
ROLLBACK TRAN myTran
END
ELSE
BEGIN
COMMIT TRAN myTran
END
BEGIN TRAN
。
END TRAN
循环体
if @@error!=0
rollback tran
else
commit tran