存储过程中的事务和回滚

问题描述

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

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

推荐答案

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