SQL 如何修复 BEGIN 和 COMMIT 语句数量不匹配的问题？

How do I fix mismatching number of BEGIN and COMMIT statements?

提问by Matt Elhotiby

I am getting this error:

我收到此错误：

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

EXECUTE 之后的事务计数表示 BEGIN 和 COMMIT 语句的数量不匹配。先前计数 = 2，当前计数 = 3。

But I don't know enough about SQL Server to stop the error.

但我对 SQL Server 的了解不够，无法阻止错误。

Here is my DROP PROCEDUREcommand:

这是我的DROP PROCEDURE命令：

--Specify database in which to uninstall procedure
USE SalesLogix_Dev
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'usp_matt_db_tasks')
            AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE usp_matt_db_tasks
GO

And here is the CREATE PROCEDURE:

这是CREATE PROCEDURE：

--Specify database in which to install procedure
USE SalesLogix_Dev
GO

--Drop existing objects in order to guanrantee error-free install
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'usp_matt_db_tasks')
            AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE usp_matt_db_tasks
GO


CREATE PROCEDURE usp_matt_db_tasks
    -- Add the parameters for the stored procedure here

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    BEGIN TRANSACTION
INSERT INTO [SalesLogix_Dev].[sysdba].[LEAD] (
    CREATEUSER,
    CREATEDATE,
    MODIFYUSER,
    MODIFYDATE,
    FIRSTNAME,
    ACCOUNTMANAGERID,
    ASSIGNDATE,
    COMPANY,
    COMPANY_UC,
    EMAIL,
    DONOTSOLICIT,
    ISPRIMARY,
    LEADSOURCEID,
    SECCODEID,
    STATUS,
    LASTNAME,
    LASTNAME_UC,
    INDUSTRY,
    NOTES,
    HOMEPHONE) 
SELECT 
       ,'something'
       ,CURRENT_TIMESTAMP
       ,'something'    
       ,CURRENT_TIMESTAMP
       ,replace(firstname, '"', '')
       ,'something'
       ,CURRENT_TIMESTAMP
       ,replace(company, '"', '')
       ,replace(UPPER(company), '"', '')
       ,replace(email, '"', '')
       ,'1'
       ,'T'
       ,''
       ,'SYST00000001'
       ,'New'
       ,replace(lastname, '"', '')
       ,replace(UPPER(lastname), '"', '')
       ,replace(department, '"', '')
       ,replace(comments, '"', '')
       ,replace(phone, '"', '')

  FROM [SalesLogix_Dev].[sysdba].[CSVTemp]

update  [SalesLogix_Dev].[sysdba].[LEAD] set LEAD_ADDRESSID = 'Q' + LEADID where DONOTSOLICIT = 1

INSERT INTO [SalesLogix_Dev].[sysdba].[LEAD_ADDRESS] (
    LEAD_ADDRESSID,
    LEADID,
    CREATEUSER,
    CREATEDATE,
    MODIFYUSER,
    MODIFYDATE,
    ISMAILING,
    ISPRIMARY) 
SELECT 
      LEAD_ADDRESSID
     ,LEADID
     ,'something'
     ,CURRENT_TIMESTAMP
     ,'something'      
     ,CURRENT_TIMESTAMP
     ,'T'
     ,'T'

  FROM [SalesLogix_Dev].[sysdba].[LEAD] where DONOTSOLICIT = 1

  update  [SalesLogix_Dev].[sysdba].[LEAD] set DONOTSOLICIT = 0 where DONOTSOLICIT = 1
  DROP TABLE [SalesLogix_Dev].[sysdba].[CSVTemp]
  ROLLBACK  TRANSACTION
COMMIT TRANSACTION

END

And finally I execute as follows:

最后我执行如下：

USE SalesLogix_Dev
GO

EXEC usp_matt_db_tasks;

回答by brain

Looks like you're rolling back the transaction, then trying to commit.

看起来您正在回滚事务，然后尝试提交。

Both should end a "begin transaction".

两者都应该结束“开始交易”。

回答by Aaron Bertrand

To drop all the procedures with the name usp_matt_db_tasksyou can run this query:

要删除具有该名称的所有过程，usp_matt_db_tasks您可以运行以下查询：

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += 'DROP PROCEDURE ' 
    + SCHEMA_ID(name) + '.'
    + QUOTENAME(name) + ';'
    FROM sys.procedures WHERE name = 'usp_matt_db_tasks';

EXEC sp_executesql @sql;

You should rollback any transactions that are currently active, close your current window, then create procedure in a new query window:

您应该回滚当前活动的所有事务，关闭当前窗口，然后在新的查询窗口中创建过程：

CREATE PROCEDURE dbo.usp_matt_db_tasks
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION;

    INSERT INTO [SalesLogix_Dev].[sysdba].[LEAD] 
    (
      CREATEUSER,
      ...
      HOMEPHONE
    ) 
    SELECT 
          ,'something'
          ...
          ,replace(phone, '"', '')
    FROM [SalesLogix_Dev].[sysdba].[CSVTemp];

    UPDATE [SalesLogix_Dev].[sysdba].[LEAD] 
      SET LEAD_ADDRESSID = 'Q' + LEADID 
      WHERE DONOTSOLICIT = 1;

    INSERT INTO [SalesLogix_Dev].[sysdba].[LEAD_ADDRESS] 
    (
      LEAD_ADDRESSID,
              ...
      ISPRIMARY
    ) 
    SELECT 
      LEAD_ADDRESSID
              ,...
      ,'T'
    FROM [SalesLogix_Dev].[sysdba].[LEAD]
    WHERE DONOTSOLICIT = 1;

    UPDATE [SalesLogix_Dev].[sysdba].[LEAD] 
      SET DONOTSOLICIT = 0 
      WHERE DONOTSOLICIT = 1;

    DROP TABLE [SalesLogix_Dev].[sysdba].[CSVTemp];

    COMMIT TRANSACTION;
END
GO

Now when you call this procedure you should always use EXEC dbo.usp_matt_db_tasks;and you should probably consider adding some error handling so that you can properly rollback the transaction in case something goes wrong.

现在，当您调用此过程时，您应该始终使用它，EXEC dbo.usp_matt_db_tasks;并且您可能应该考虑添加一些错误处理，以便在出现问题时可以正确回滚事务。