Click here to Skip to main content
15,895,142 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:-
SQL
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
Posted
Updated 15-Apr-15 2:46am
v2

1 solution

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[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900