存储过程中的事务和回滚 [英] Transaction and Rollback in Store Procedure

问题描述

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

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语句跳转到您回滚事务的底部。



许多在线示例，例如 http://stackoverflow.com/questions/1749719/sql-server-transactions-回滚错误 [ ^ ]



http://stackoverflow.com/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 http://stackoverflow.com/questions/1749719/sql-server-transactions-roll-back-on-error[^]

http://stackoverflow.com/questions/3935900/how-to-commit-and-rollback-transaction-in-sql-server[^]

Also, a good article here is SQL Server Transactions and Error Handling[^]

这篇关于存储过程中的事务和回滚的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！