Commit Rollback Transaction
ALTER procedure [dbo].[acc_to_acc_sp]
@Acc_From_ID int, -- Foreign Key
@Acc_To_ID int, -- Foreign Key
--@Trans_ID int, -- Auto Generate, Primary Key
@Bank_ID int, -- Foreign Key
@Branch_ID int, -- Foreign Key
@Amount decimal(18, 2),
@Date datetime,
@eve varchar(2)
as
declare
@f_ID int,
@t_ID int,
@debit decimal(18,2),
@debit2 decimal(18,2),
@counts int,
@credit_counts int,
@credits decimal(18,2),
@Trans_ID int,
@bool bit,
@bool1 bit
set @bool=0
set @bool1=0
select @f_ID = Acc_ID from account_holder where acc_number=@Acc_From_ID
select @t_ID = Acc_ID from account_holder where acc_number=@Acc_To_ID
if @eve='i'
begin transaction
begin
insert into acc_to_acc values(@f_ID,@t_ID,@Bank_ID,@Branch_ID,@Amount,@Date)
select @trans_id= max(trans_id) from acc_to_acc where acc_from_id=@f_id
select @counts=count(*) from transaction_details where Acc_ID=@F_ID
select @credit_counts=count(*) from transaction_details where Acc_ID=@t_ID
if (@counts=0)
begin
select @debit= deposit_amt from account_holder where acc_id=@f_id
print @debit
print @amount
if exists (select * from account_holder where deposit_amt>=@amount and acc_iD=@f_ID)
begin
insert into transaction_details values(@f_ID,@Trans_ID,@Date,0,@Amount,@debit-@Amount)
set @bool=1
end
end
else
begin
select @debit=Balance from transaction_details where acc_id=@f_id and trans_id in
(select max(trans_id) from transaction_details where acc_id=@f_id)
print @debit
if exists (select * from transaction_details where @debit>=@amount and acc_iD=@f_ID)
begin
insert into transaction_details values(@f_ID,@Trans_ID,@Date,0,@Amount,@debit-@Amount)
set @bool=1
end
end
if(@credit_counts=0)
begin
select @credits= deposit_amt from account_holder where acc_id=@t_id
print @credits
print @amount
if exists (select * from account_holder where deposit_amt>=@amount and acc_iD=@t_ID)
begin
insert into transaction_details values(@t_ID,@Trans_ID,@Date,@Amount,0,@credits+@Amount)
set @bool1=1
end
end
else
begin
select @credits=Balance from transaction_details where acc_id=@t_id and trans_id in
(select max(trans_id) from transaction_details where acc_id=@t_id)
print @credits
if exists (select * from transaction_details where @credits>=@amount and acc_iD=@t_ID)
begin
insert into transaction_details values(@t_ID,@Trans_ID,@Date,@Amount,0,@credits+@Amount)
set @bool1=1
end
end
print @bool
if(@bool=0 or @bool1=0)
begin
rollback transaction
end
else
commit transaction
end