Although I am able to create stored procedure successfully I am getting "TRANSACTION BEGIN END MISMATCH" error upon using it. The stored Procedure works fine when I remove transaction.
alter procedure Proc_LoanRepayment
@LASAcctno [Varchar] (15), @EntryDate [Varchar] (8), @ValueDate [Varchar] (8),@ModeofPayment varchar(20),
@ChqNo varchar(20),@ChqDate varchar(8),@ChqAmt money,@CstBnkNo varchar(20),@Cstbnkid varchar(20),
@CmpBnkNo varchar(20),@Cmpbnkid varchar(20),
@Narration1 [Varchar] (100), @Narration2 [Varchar] (100), @EntryType varchar(30),
@PostingString [Varchar] (max)
as
BEGIN TRAN
declare @string as varchar(max)
DECLARE Cur_A CURSOR FOR
SELECT * FROM DBO.split( @PostingString , ',')
OPEN Cur_a
FETCH NEXT FROM cur_a INTO @string
WHILE @@FETCH_STATUS = 0
BEGIN
IF (object_id('TempDB..#Temp')) IS NOT NULL
BEGIN
DROP TABLE #Temp
END
select * into #temp from dbo.Split(@string,'|')
declare @LoanNo as varchar(20), @BankAcct as varchar(20) , @TDS as money, @LASPAC as money , @INTRND as money, @INTRAC as money,@STAXPLRVL as money, @PNLINT as money, @OVRDUEINT as money,@STMPDTYRVL as money,
@PROFESRVL as money, @DOCCHGRVL as money,@CHQBNCRVL as money
alter table #temp add srno int identity
select @LoanNo = items from #temp where srno=1
select @bankAcct = items from #temp where srno=2
select @TDS = items from #temp where srno=3
select @LASPAC = items from #temp where srno=4
select @INTRND = items from #temp where srno=5
select @INTRAC = items from #temp where srno=6
select @STAXPLRVL = items from #temp where srno=7
select @PNLINT = items from #temp where srno=8
select @OVRDUEINT = items from #temp where srno=9
select @STMPDTYRVL = items from #temp where srno=10
select @PROFESRVL = items from #temp where srno=11
select @DOCCHGRVL = items from #temp where srno=12
select @CHQBNCRVL= items from #temp where srno=13
insert into Tbl_BankEntry(LASAcctno , EntryDate , ValueDate ,ModeofPayment,
ChqNo ,ChqDate ,ChqAmt,CstBnkNo ,Cstbnkid ,
CmpBnkNo ,Cmpbnkid ,
Narration1 , Narration2 , LoanNo, BankAcct,TDS , LASPAC , INTRND, INTRAC ,STAXPLRVL , PNLINT , OVRDUEINT ,STMPDTYRVL , PROFESRVL, DOCCHGRVL ,CHQBNCRVL,status,mkrdt,mkrid,EntryType )
values(
@LASAcctno , @EntryDate , @ValueDate ,@ModeofPayment,
@ChqNo ,@ChqDate ,@ChqAmt,@CstBnkNo ,@Cstbnkid ,
@CmpBnkNo ,@Cmpbnkid ,
@Narration1 , @Narration2 , @LoanNo, @BankAcct,@TDS , @LASPAC , @INTRND, @INTRAC ,@STAXPLRVL , @PNLINT , @OVRDUEINT ,@STMPDTYRVL , @PROFESRVL, @DOCCHGRVL ,@CHQBNCRVL,'P',GETDATE(),'c97176',@EntryType
)
FETCH NEXT FROM Cur_a INTO @string
END
CLOSE cur_a
DEALLOCATE cur_a
RETURN
COMMIT TRAN