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