Sql TRANSACTION开始结束不匹配

问题描述

虽然我能够成功创建存储过程但是在使用它时会出现TRANSACTION BEGIN END MISMATCH错误。当我删除交易时，存储过程正常工作。

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
  --  declare @temp table (srno int identity,items varchar(max))              
 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   

推荐答案

这篇关于Sql TRANSACTION开始结束不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！