存储过程中的事务和回滚

本文介绍了存储过程中的事务和回滚的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

先生，我有一个存储过程，其中包含多个查询..我想在其中添加事务和回滚。因此，当任何查询中出现任何错误时，则所有查询都将回滚。我的程序是： -

Sir i have a Store Procedure with more then one query in it..i want to add Transaction and rollback in that. So when any Error Occur in any query then all query are rollback. My Procedure is:-

ALTER PROCEDURE [dbo].[FeeReceipt] @DocId int , @SessionID int, @StudentID smallint, @ClassesID int, @SectionID int, @FatherName varchar(250) , @AdmissionNo varchar(50), @Student varchar(100), @ScholarType varchar(20), @Stopage varchar(50), @RouteNo varchar(50), @Remark varchar(250), @UserID int, @FeeReceived int, @FineReceived int, @RoundOff int, @Total int, @Dated datetime, @TransTypeID int, @Narration varchar(100), @DocNo varchar(50), @AccountID int, @Paymentby varchar(50), @PaymentDesc varchar(100), @DOC datetime, @Bank int, @Branch varchar(50), @AccountIDRoundOff int, @AmountRoundOff int, @AmountIDFine int, ----------- @Str varchar(500), -------- @Str1 varchar(250), --------------- @new_identity nvarchar(100) OUTPUT AS BEGIN Declare @Voucherid int SET NOCOUNT ON; Insert into voucher (CID,StudentID,Dated,TransTypeID,Narration,Amount,DocNo,UserID) values(0,@StudentID,@Dated,@TransTypeID,@Narration,@Total,@DocNo,@UserID) SELECT @Voucherid = SCOPE_IDENTITY() FROM Voucher Insert into FeeTrans (StudentID,FeeTransID,Docno,Dated,ClassesID,SectionID,FatherName,AdmissionNo,Student,FeeReceived,FineReceived,ScholarType,RouteNo,Stopage,RoundOff,Total,Remark) values(@StudentID,@Voucherid,@DocNo,@Dated,@ClassesID,@SectionID,@FatherName,@AdmissionNo,@Student,@FeeReceived ,@FineReceived,@ScholarType,@RouteNo,@Stopage,@RoundOff,@Total,@Remark) DELETE FROM VoucherItem WHERE VoucherID =@Voucherid INSERT INTO VoucherItem (VoucherID, SideID, AccountID, Amount, Paymentby, PaymentDesc, DOC, Bank, Branch, AgainstID) VALUES (@Voucherid,1,@AccountID,@Total,@Paymentby,@PaymentDesc,@DOC,@Bank,@Branch,Null) If (@AmountRoundOff) < 0 begin INSERT INTO VoucherItem (VoucherID, SideID, AccountID, Amount, AgainstID) VALUES (@Voucherid,1,@AccountIDRoundOff,abs(@AmountRoundOff),Null) end Delete from FeeTransItem where FeeTransID=@DocId if not @Str ='' begin Insert into FeeTransItem(FeeTransID,SessionID,FeeHeadID,MNameID,Amount) select * from [dbo].[fn_ArrayTable] (@VoucherID,@SessionID, @str ,'/') end if not @Str1='' begin Insert into FeeTransItem(FeeTransID,SessionID,FeeHeadID,Amount) select * from [dbo].[fn_ArrayTable2] (@VoucherID, @str1 ,'/') end INSERT INTO VoucherItem (VoucherID, SideID, AccountID, Amount) SELECT FeeTransID, 2 AS Expr2, FeeheadID, SUM(Amount) AS Expr1 From FeeTransItem Where (FeeTransID = @VoucherID ) GROUP BY FeeTransID, FeeheadID If (@FeeReceived) < 0 begin INSERT INTO VoucherItem (VoucherID, SideID, AccountID, Amount, AgainstID) VALUES (@VoucherID,1,@AmountIDFine,abs(@FeeReceived),Null) end IF (@FeeReceived) > 0 begin INSERT INTO VoucherItem (VoucherID, SideID, AccountID, Amount, AgainstID) VALUES (@VoucherID,2,@AmountIDFine,abs(@FeeReceived),Null) end If (@RoundOff) > 0 begin INSERT INTO VoucherItem (VoucherID, SideID, AccountID, Amount, AgainstID) VALUES (@VoucherID,2,@AccountIDRoundOff,abs(@AmountRoundOff),Null) end UPDATE VoucherItem SET AgainstID = (SELECT TOP (1) AccountID FROM VoucherItem AS VoucherItem_1 Where (VoucherID = VoucherItem.VoucherID) And (SideID <> VoucherItem.SideID) ORDER BY VoucherItemID) WHERE (VoucherID = @VoucherID) SELECT @new_identity= convert(varchar, @Voucherid) End

推荐答案

有很多方法可以处理它。您可以使用Try Catch或者您可以检查@@ Error并使用GOTO语句跳转到您回滚事务的底部。 许多在线示例，例如 stackoverflow/questions/1749719/sql-server-transactions-回滚错误 [ ^ ] stackoverflow/questions/3935900/how-to-commit-and-rollback-transaction-in-sql-server [ ^ ] 另外，这里有一篇好文章是 SQL Server事务和错误处理 [ ^ ] There are numerous ways of handling it. You can use Try Catch or you can check @@Error and use a GOTO statement to jump to the bottom where you rollback the transaction. Many examples online, such as stackoverflow/questions/1749719/sql-server-transactions-roll-back-on-error[^] stackoverflow/questions/3935900/how-to-commit-and-rollback-transaction-in-sql-server[^] Also, a good article here is SQL Server Transactions and Error Handling[^]