EXECUTE之后的事务计数表示BEGIN和COMMIT语句的数量不匹配。先前的计数= 0，当前计数= 1。 [英] Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

问题描述

我正在使用有点冗长的存储过程。

I am using stored procedure which is bit lengthy.

USE [BusOprtn]
GO
/****** Object:  StoredProcedure [dbo].[AddBusLog]    Script Date: 03/28/2014 21:49:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  PROCEDURE [dbo].[AddBusLog]
      @breakdownInsert dbo.BreakdownInsert READONLY,
      @breakdownUpdate dbo.BreakdownUpdate READONLY,
      @driverAttendanceInsert dbo.DriverAttendanceInsert READONLY,
      @driverAttendanceUpdate dbo.DriverAttendanceUpdate READONLY,
      @fuelInsert dbo.FuelInsert READONLY,
      @fuelUpdate dbo.FuelUpdate READONLY,
      @transactionInsert dbo.TransactionInsert READONLY,
      @transactionUpdate dbo.TransactionUpdate READONLY,
       
      @busId [bigint],
      @routeId [bigint],
      @routeSeries [bigint],
      @exitTime [float],
      @date [date]
      ,@LogNo nvarchar(50)
      ,@ActualStartKm [float]
      ,@ActualEndKm [float]
      ,@FuelBeforeTrip [float]
      ,@isBreakDown [bit]
      ,@ReturnTime [float]
      ,@Remark nvarchar(max)
   
      
     
AS
BEGIN

declare @logId [bigint];
	  SET NOCOUNT ON;
	  
	  begin try
	  begin transaction
		update  [BusOprtn].[dbo].[Bus Log] set [busId]=@busId ,[routeNo]=@routeId,[RouteSeries]=@routeSeries,[ExitTime]=@exitTime
		,[Date]=@date ,[LogNo]=@LogNo,[ActualStartKm]=@ActualStartKm,[FuelBeforeTrip]=@FuelBeforeTrip,[isBreakDown]=@isBreakDown, [ReturnTime]=@ReturnTime,
         [Remark]=@Remark where [Id]=@logId;
		
		insert into [BusOprtn].[dbo].[BreakDown] ( [BreakDownSolution],[Shift],[StartTime],[EndTime]
                  ,[StartTimeAttender],[EndTimeAttender],[isOnRoad] ,[faultId],[breakDownBusNo],[isBreadDownVerfied],[BusLogId],[RunKm],[DeadKm],[stand]) select [BreakDownSolution],[Shift],[StartTime],[EndTime]
                  ,[StartTimeAttender],[EndTimeAttender],[isOnRoad] ,[faultId],[breakDownBusNo],0,@logId,[RunKm],[DeadKm] ,[stand]from @breakdownInsert;
                  
       update [BusOprtn].[dbo].[BreakDown]  set [BreakDownSolution]=bdu.[BreakDownSolution],[Shift]=bdu.[Shift],[StartTime]=bdu.[StartTime],[EndTime]=bdu.[EndTime]
                  ,[StartTimeAttender]=bdu.[StartTimeAttender],[EndTimeAttender]=bdu.[EndTimeAttender],[isOnRoad]=bdu.[isOnRoad] ,[faultId]=bdu.[faultId],[breakDownBusNo]=bdu.[breakDownBusNo],
                  [isBreadDownVerfied]=0,[RunKm]=bdu.[RunKm],[DeadKm]=bdu.[DeadKm], [stand]=bdu.[stand]
                 from [BusOprtn].[dbo].[BreakDown] as bd  inner join  @breakdownUpdate as bdu on bd.[Id] =  bdu.[Id]   ;
	 
	 
	 
	    insert into [BusOprtn].dbo.DriverAttendance ([LogId],[DriverId],[Shift],[AttendanceDateTime],[IsFullDay],[IsDressed],[StandMiss])select @logId ,
				[Driver],[shift],[DateTime],[IsFullDay],[IsDressed],[StandMiss] from dbo.DriverAttendanceInsert;
				
	    update [BusOprtn].dbo.DriverAttendance set [DriverId]=dau.[Driver],[Shift]=dau.[Shift],[AttendanceDateTime]=dau.[DateTime],[IsFullDay]=dau.[IsFullDay],[IsDressed]=dau.[IsDressed],[StandMiss]=dau.[StandMiss]	from [BusOprtn].dbo.DriverAttendance  as da inner join 
	  @driverAttendanceUpdate as dau on da.[Id]=dau.[Id];
	  
	  
	  insert into [dbo].[Transaction] ([LogId],[DbCrAmount],[CrediEmpId],[DebitEmpId],[Date],[TransferReason],[isAccountAffected]) 
	   select @logId ,[Amount],[CreditEmpId],[DebitEmpId],[TransactionDate],[TransactionFor],0 from @transactionInsert
	  
	  
	  update [dbo].[Transaction] set [DbCrAmount]=tu.[Amount],[CrediEmpId]=tu.[CreditEmpId],[DebitEmpId]=tu.[DebitEmpId],[Date]=tu.[TransactionDate],[TransferReason]=tu.[TransactionFor],[isAccountAffected]=0
	  from [dbo].[Transaction] as t inner join @transactionUpdate as tu on t.[Id]=tu.[Id]  ;
	  
	  
	  insert into dbo.FuelUpdate ([LogId],[BillNo],[Rate],[Quantity],[DriverId]) select @logId ,[BillNo] ,[rate] ,[quantity] ,[Driver] from @fuelInsert;
	 
	  update dbo.FuelUpdate set [BillNo]=[BillNo],[Rate]=[rate],[Quantity]=[quantity],[DriverId]= [Driver] from dbo.FuelUpdate as f inner join @fuelUpdate as fu on f.[Id]=fu.[Id]	 
	  commit transaction
		end try
		BEGIN CATCH
		rollback transaction;
		declare @ErrorMessage nvarchar(max), @ErrorSeverity 

int, @ErrorState int;
    select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast

(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), 

@ErrorState = ERROR_STATE();
    
    raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
		END CATCH<pre lang="SQL">

END



当我打电话来自C＃的上述存储过程，我得到异常

END

When i call the above stored procedure from C#, i get exception

Invalid object name 'dbo.DriverAttendanceInsert'.

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

。请问是否有任何混淆，因为调用代码和用户定义的表类型代码很长并且会增加复杂性。什么都不知道这个例外。

. Please ask if have any confusion because calling code and user defined table type code is lengthy and will add complexity. Don't know anything about this exception.

推荐答案

对于这样一个愚蠢的问题只是表名错误抱歉。

Sorry about such a foolish question simply table name mistake.

这篇关于EXECUTE之后的事务计数表示BEGIN和COMMIT语句的数量不匹配。先前的计数= 0，当前计数= 1。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！