sql中出现错误:EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配。

Question

消息 266，级别 16，状态 2，过程 e_OperationInform，第 47 行
EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数目不匹配。上一计数 = 2，当前计数 = 3
代码如下，请高人帮忙看看
ALTER PROCEDURE [dbo].[e_OperationInform]
@UserName varchar(50),
@pramXml Text
--@Result char(1) OUTPUT        -- 返回结果
AS
BEGIN
  DECLARE @idoc int
  DECLARE @examName varchar(800)
  DECLARE @limitMins varchar(800)
  DECLARE @neecCheck varchar(800)
  DECLARE @needPrompt varchar(800)
  DECLARE @setAnswer varchar(800)
  DECLARE @beginDate varchar(800)
  DECLARE @endDate varchar(800)
  DECLARE @paperId varchar(800)
  
  DECLARE @exemId int
  
  EXEC sp_xml_preparedocument @idoc OUTPUT,@pramXml  set @paperId = (SELECT * FROM OPENXML (@idoc,'/xml/PaperId',1) WITH (name varchar(4000))) 
set @examName = (SELECT * FROM OPENXML (@idoc,'/xml/ExamName',1) WITH (name varchar(4000)))                      
set @limitMins = (SELECT * FROM OPENXML (@idoc,'/xml/LimitMins',1) WITH (name varchar(4000)))  
set @neecCheck = (SELECT * FROM OPENXML (@idoc,'/xml/NeecCheck',1) WITH (name varchar(4000))) 
set @needPrompt = (SELECT * FROM OPENXML (@idoc,'/xml/NeedPrompt',1) WITH (name varchar(4000)))                      
set @setAnswer = (SELECT * FROM OPENXML (@idoc,'/xml/SetAnswer',1) WITH (name varchar(4000)))                                          
set @beginDate = (SELECT * FROM OPENXML (@idoc,'/xml/BeginDate',1) WITH (name varchar(4000)))                                          
set @endDate = (SELECT * FROM OPENXML (@idoc,'/xml/EndDate',1) WITH (name varchar(4000)))

INSERT INTO dbo.ExamInfo(ExamName,PaperId,[Status],LimitMins,BeginDate,EndDate,NeecCheck,NeedPrompt,SetAnswer,CreateUser) values(@examName,@paperId,2,@limitMins,@beginDate,@endDate,@neecCheck,@needPrompt,@setAnswer,@UserName)
    set @exemId = @@IDENTITY 
SELECT * into #tmp FROM OPENXML (@idoc,'/xml/Cs',1) WITH (ClassId int,StudentId int) 
EXEC sp_xml_removedocument @idoc IF(@exemId > 0)
BEGIN 
BEGIN TRANSACTION 
INSERT INTO ExamParperStatus ([ExamId],[ClassId],[StId],[Status],[IsCheck],[CheckTeach],[CreateUser])
SELECT @exemId,t.ClassId,(t.StudentId),0,@neecCheck,@UserName,@UserName FROM #tmp t

INSERT INTO ExamQuestionStatus([StudId],[ExamId],[PaperId],[QuestionId],[SubjectId],[CreateUser])
SELECT m.StudentIdd,@exemId,@paperId,e.QuesId,e.SubjectId,@UserName FROM #tmp m, ExamQuestion e WHERE e.PaperId = @paperId
--上面的sql语句报错的话就不走下面代码
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN COMMIT TRANSACTION
END
    End
END

Answer 1

ALTER PROCEDURE [dbo].[e_OperationInform]
@UserName varchar(50),
@pramXml Text
--@Result char(1) OUTPUT        -- 返回结果
AS
BEGIN
  DECLARE @idoc int
  DECLARE @examName varchar(800)
  DECLARE @limitMins varchar(800)
  DECLARE @neecCheck varchar(800)
  DECLARE @needPrompt varchar(800)
  DECLARE @setAnswer varchar(800)
  DECLARE @beginDate varchar(800)
  DECLARE @endDate varchar(800)
  DECLARE @paperId varchar(800)
  
  DECLARE @exemId int
  
  EXEC sp_xml_preparedocument @idoc OUTPUT,@pramXml  set @paperId = (SELECT * FROM OPENXML (@idoc,'/xml/PaperId',1) WITH (name varchar(4000))) 
set @examName = (SELECT * FROM OPENXML (@idoc,'/xml/ExamName',1) WITH (name varchar(4000)))                      
set @limitMins = (SELECT * FROM OPENXML (@idoc,'/xml/LimitMins',1) WITH (name varchar(4000)))  
set @neecCheck = (SELECT * FROM OPENXML (@idoc,'/xml/NeecCheck',1) WITH (name varchar(4000))) 
set @needPrompt = (SELECT * FROM OPENXML (@idoc,'/xml/NeedPrompt',1) WITH (name varchar(4000)))                      
set @setAnswer = (SELECT * FROM OPENXML (@idoc,'/xml/SetAnswer',1) WITH (name varchar(4000)))                                          
set @beginDate = (SELECT * FROM OPENXML (@idoc,'/xml/BeginDate',1) WITH (name varchar(4000)))                                          
set @endDate = (SELECT * FROM OPENXML (@idoc,'/xml/EndDate',1) WITH (name varchar(4000)))

INSERT INTO dbo.ExamInfo(ExamName,PaperId,[Status],LimitMins,BeginDate,EndDate,NeecCheck,NeedPrompt,SetAnswer,CreateUser) values(@examName,@paperId,2,@limitMins,@beginDate,@endDate,@neecCheck,@needPrompt,@setAnswer,@UserName)
    set @exemId = @@IDENTITY 
SELECT * into #tmp FROM OPENXML (@idoc,'/xml/Cs',1) WITH (ClassId int,StudentId int) 
EXEC sp_xml_removedocument @idoc IF(@exemId > 0)
BEGIN 
BEGIN TRANSACTION 
INSERT INTO ExamParperStatus ([ExamId],[ClassId],[StId],[Status],[IsCheck],[CheckTeach],[CreateUser])
SELECT @exemId,t.ClassId,(t.StudentId),0,@neecCheck,@UserName,@UserName FROM #tmp t

INSERT INTO ExamQuestionStatus([StudId],[ExamId],[PaperId],[QuestionId],[SubjectId],[CreateUser])
SELECT m.StudentId,@exemId,@paperId,e.QuesId,e.SubjectId,@UserName FROM #tmp m, ExamQuestion e WHERE e.PaperId = @paperId

IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END
    End
END

Answer 2

ALTER PROCEDURE [dbo].[e_OperationInform]
    @UserName varchar(50) ,
    @pramXml text
--@Result char(1) OUTPUT -- 返回结果
AS 
    BEGIN
        DECLARE @idoc int
        DECLARE @examName varchar(800)
        DECLARE @limitMins varchar(800)
        DECLARE @neecCheck varchar(800)
        DECLARE @needPrompt varchar(800)
        DECLARE @setAnswer varchar(800)
        DECLARE @beginDate varchar(800)
        DECLARE @endDate varchar(800)
        DECLARE @paperId varchar(800)
   
        DECLARE @exemId int
   
        EXEC sp_xml_preparedocument @idoc OUTPUT, @pramXml          SET @paperId = ( SELECT *
                         FROM   OPENXML (@idoc,'/xml/PaperId',1) WITH (name varchar(4000))
                       )  
        SET @examName = ( SELECT    *
                          FROM      OPENXML (@idoc,'/xml/ExamName',1) WITH (name varchar(4000))
                        )   
        SET @limitMins = ( SELECT   *
                           FROM     OPENXML (@idoc,'/xml/LimitMins',1) WITH (name varchar(4000))
                         )   
        SET @neecCheck = ( SELECT   *
                           FROM     OPENXML (@idoc,'/xml/NeecCheck',1) WITH (name varchar(4000))
                         )  
        SET @needPrompt = ( SELECT  *
                            FROM    OPENXML (@idoc,'/xml/NeedPrompt',1) WITH (name varchar(4000))
                          )   
        SET @setAnswer = ( SELECT   *
                           FROM     OPENXML (@idoc,'/xml/SetAnswer',1) WITH (name varchar(4000))
                         )   
        SET @beginDate = ( SELECT   *
                           FROM     OPENXML (@idoc,'/xml/BeginDate',1) WITH (name varchar(4000))
                         )   
        SET @endDate = ( SELECT *
                         FROM   OPENXML (@idoc,'/xml/EndDate',1) WITH (name varchar(4000))
                       )        INSERT  INTO dbo.ExamInfo
                (
                  ExamName ,
                  PaperId ,
                  [Status] ,
                  LimitMins ,
                  BeginDate ,
                  EndDate ,
                  NeecCheck ,
                  NeedPrompt ,
                  SetAnswer ,
                  CreateUser
                )
        VALUES  (
                  @examName ,
                  @paperId ,
                  2 ,
                  @limitMins ,
                  @beginDate ,
                  @endDate ,
                  @neecCheck ,
                  @needPrompt ,
                  @setAnswer ,
                  @UserName
                )
        SET @exemId = @@IDENTITY  
        SELECT  *
        INTO    #tmp
        FROM    OPENXML (@idoc,'/xml/Cs',1) WITH (ClassId int,StudentId int)  
        EXEC sp_xml_removedocument @idoc        IF ( @exemId > 0 ) 
            BEGIN  
                BEGIN TRANSACTION  
                INSERT  INTO ExamParperStatus
                        (
                          [ExamId] ,
                          [ClassId] ,
                          [StId] ,
                          [Status] ,
                          [IsCheck] ,
                          [CheckTeach] ,
                          [CreateUser]
                        )
                        SELECT  @exemId ,
                                t.ClassId ,
                                ( t.StudentId ) ,
                                0 ,
                                @neecCheck ,
                                @UserName ,
                                @UserName
                        FROM    #tmp t                INSERT  INTO ExamQuestionStatus
                        (
                          [StudId] ,
                          [ExamId] ,
                          [PaperId] ,
                          [QuestionId] ,
                          [SubjectId] ,
                          [CreateUser]
                        )
                        SELECT  m.StudentIdd ,
                                @exemId ,
                                @paperId ,
                                e.QuesId ,
                                e.SubjectId ,
                                @UserName
                        FROM    #tmp m ,
                                ExamQuestion e
                        WHERE   e.PaperId = @paperId
--上面的sql语句报错的话就不走下面代码
                IF @@ERROR <> 0 
                    BEGIN
                        ROLLBACK TRANSACTION
                    END
                ELSE 
                    BEGIN                        COMMIT TRANSACTION
                    END
            END
    END

Answer 3

俺都是用TRY CATCH的，从来木有过这个问题。友情提示，一是把你的BEGN END对的位置检查一下，二是ROLLBACK TRAN和COMMIT TRAN的时候查询一下系统计数。