在事务中创建Server过程

Question

我需要在Server事务中创建两个过程。如果失败，则需要回滚此事务中的create和任何其他已执行查询。我知道create语句必须是查询批处理中的第一个语句，但我需要知道如何处理多批事务。

BEGIN TRANSACTION

CREATE PROCEDURE [dbo].[SP_SP-1]
    @id BIGINT
AS
BEGIN
    SET NOCOUNT ON;

    -- SQL statements 
END
GO

CREATE PROCEDURE [dbo].[SP_SP-2]
    @id BIGINT  
AS  
BEGIN
    SET NOCOUNT ON;

    -- SP-2 statements
END
GO

UPDATE Table 
SET Value = '1.0.0.5' 

COMMIT TRANSACTION / ROLLBACK TRANSACTION 

Answer 1

下面是在事务中执行多个批处理的一种方法。这将使用临时表来指示是否有任何批处理出错，并相应地执行最终的COMMIT或ROLLLBACK。

另一种方法是封装必须在单语句批处理中的语句(CREATE PROCEDURE、CREATE VIEW等)。但是，当文字文本中的引号必须转义时，这种情况可能会变得相当丑陋。

CREATE TABLE #errors (error varchar(5));
GO

BEGIN TRANSACTION
GO

CREATE PROCEDURE [dbo].[USP_SP-1]
    @id bigint 
AS
BEGIN
    SET NOCOUNT ON;
    -- SP Statments 
END;
GO
IF @@ERROR <> 0 INSERT INTO #errors VALUES('error');
GO

CREATE PROCEDURE [dbo].[USP_SP-2]
     @id BIGINT  
AS  
BEGIN
    SET NOCOUNT ON;
    -- SP-2 Statments
END;
GO
IF @@ERROR <> 0 INSERT INTO #errors VALUES('error');
GO

UPDATE Table SET Value='1.0.0.5' 
GO
IF @@ERROR <> 0 INSERT INTO #errors VALUES('error');
GO

IF EXISTS(SELECT 1 FROM #errors)
BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK;
END
ELSE
BEGIN
    IF @@TRANCOUNT > 0 COMMIT;
END;
GO

IF OBJECT_ID(N'tempdb..#errors', 'U') IS NOT NULL
    DROP TABLE #errors;
GO

Answer 2

我建议你在在嵌套Server存储过程中处理事务上更多地研究这个问题。

从一开始，你的语法就错了。您不能开始一个事务，然后创建一个过程，您需要做的正好相反：

CREATE PROCEDURE [dbo].[SP_SP-1]
@id bigint  
AS
BEGIN

   BEGIN TRY

      BEGIN TRANSACTION

           SET NOCOUNT ON;
        -- SP-2 Statments

          Update Table set Value='1.0.0.5'

   END TRY

   BEGIN CATCH 
    --handle error and perform rollback
         ROLLBACK  
      SELECT ERROR_NUMBER() AS ErrorNumber
      SELECT ERROR_MESSAGE() AS ErrorMessage   
   END CATCH
END

在试图在事务范围内执行更新时，最好使用TRY和CATCH。

请阅读更多和调查使用我提供的链接，以获得更大的图片。

Answer 3

要使用事务，您需要知道事务的含义。它的意思是“工作单位，无论是提交状态还是回滚状态”。

因此，当您使用事务处理时，您必须知道声明的位置和关闭的位置。因此，您必须在父过程中启动和结束事务，而不是它将作为一个工作单元工作，也就是说，无论DML语句的查询执行是什么，它都使用相同的事务。

我不明白为什么您的update语句也超出了过程和事务部分。

应该是这样(请参阅我的注释，您可以使用TRY与c夏普相同)：

Create PROCEDURE [dbo].[SP_SP-1]
    @id bigint 
  AS
  BEGIN
   Begin Transaction
     SET NOCOUNT ON;

            -- SP Statments 
            Exec SP_SP-2 @id    --here you can pass the parameter to another procedure, but do not use transaction in another procedure, other wise it will create another transaction
    If @@Error > 0 than
        Rollback 
    Else
        Commit
    End
   END


 GO

 --Do not use transaction in another procedure, otherwise, it will create another transaction which has own rollback and commit and do not participate in the parent transaction
  Create PROCEDURE [dbo].[SP_SP-2]
     @id BIGINT  
    AS  
      BEGIN
      SET NOCOUNT ON;
      -- SP-2 Statments

       END
  GO