Sql TRANSACTION开始结束不匹配 [英] Sql TRANSACTION Begin End Mismatch

问题描述

虽然我能够成功创建存储过程但是在使用它时会出现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   

推荐答案

在返回之前放置提交

代码中没有错误句柄。如果发生某些错误，则不会回滚事务。 MISMATCH将会发生。

there is no error handle in your code. if some error occur, the transaction will not be rolled back. "MISMATCH" will happed.

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