SQL Server：无法在事务中删除和创建 SP

Question

我正在尝试放置一个存储过程，然后在交易中重新创建它，然后重新创建它。

BEGIN TRANSACTION
BEGIN TRY
IF OBJECT_ID(N'dbo.GET_DATA', N'P') IS NOT NULL
BEGIN 
    DROP PROCEDURE [dbo].[GET_DATA]
END
CREATE PROCEDURE [dbo].[GET_DATA]
    @date datetime2
AS
    SET NOCOUNT ON
BEGIN
    SELECT 
        dbo.Products.product_cod AS 'product_cod',
        dbo.Product_Types.name AS 'product_type_name',
        dbo.UM.name AS 'um_name',
        dbo.Products.category_id AS 'category_id',
        dbo.Bins_Products.bin_id AS 'product_bin_id' 
    FROM dbo.Products
        LEFT JOIN dbo.Product_Types on Products.product_type_id = Product_Types.product_type_id
        LEFT JOIN dbo.UM on Products.um_id = UM.um_id
        LEFT JOIN Bins_Products ON Bins_Products.product_id = Products.product_id
    WHERE
        Products.update_date >= @date
END
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS 'ErrorMessage'
    ROLLBACK TRANSACTION
END CATCH

当我运行上面的脚本时，我会收到以下错误：

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'PROCEDURE'.
Msg 137, Level 15, State 2, Line 31
Must declare the scalar variable "@date".

我在集合上有弯曲的线，@date。

if语句和创建语句本身都可以正常工作。

Answer 1

您可以使用EXEC在交易中创建过程，但这非常不便，因为整个身体需要逃脱。一种更好的方法是确保始终存在存储过程，然后执行ALTER，该过程不需要单独的交易：

IF OBJECT_ID('Foo', 'P') IS NULL
    EXEC ('CREATE PROCEDURE Foo AS BEGIN RETURN END;');
GO
ALTER PROCEDURE Foo(@Arg INT) AS BEGIN
    ...
END;

这种方法的另一个好处（或一个缺点，取决于您的部署过程），这使得在存储过程中的任何现有权限完好无损，这与掉落和创建不同。

Answer 2

只是为了证明此可以在交易中完成，这是一个演示脚本：

create procedure dbo.A
as
    select 1 as T
go
exec dbo.A
go
begin transaction
go
IF OBJECT_ID(N'dbo.A', N'P') IS NOT NULL
BEGIN 
    EXEC('drop procedure dbo.A')
END
go
create procedure dbo.A
as
    select penguin from sys.objects --This will fail
go
IF OBJECT_ID(N'dbo.A', N'P') IS NOT NULL
BEGIN
    commit
END
ELSE
BEGIN
    rollback transaction
END
go
exec dbo.A

尝试创建新的A过程时会产生错误，并且回滚恢复回到A的原始版本。这只能真正起作用（如在此处），其中创建新版本的A会导致硬错误，以便我们以后可以检测到并决定rollback而不是commit。

话虽如此，我仍然自己使用耶洛恩的回答。

Answer 3

过程定义必须在其自己的批处理中。在管理工作室中，您将使用

的两行

go

在create procedure之前和end之后。不幸的是交易不能跨越多批次。

您可以在exec调用中创建过程？喜欢：

exec ('create procedure dbo.MyProc as ...');

Answer 4

使用此代码，这是我的工作示例。

根据您的表和要求对其进行修改。

USE [DatabaseName]
GO
/****** Object:  StoredProcedure [dbo].[_AdvancePaymentDelete]    Script Date: 10/04/2017 15:12:43 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_AdvancePaymentDelete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[_AdvancePaymentDelete]
GO
USE [DatabaseName]
GO
/****** Object:  StoredProcedure [dbo].[_AdvancePaymentDelete]    Script Date: 10/04/2017 15:12:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[_AdvancePaymentDelete] 
(
        @_ADVANCEPAYMENTID_PK uniqueidentifier,
        @_COMPANYID_PK uniqueidentifier ,
        @_COMPANYDETID_PK uniqueidentifier ,
        @_USERID_PK uniqueidentifier
          )
AS
BEGIN
        BEGIN TRANSACTION;
        SAVE TRANSACTION MySavePoint;
    DECLARE @ErrorMessage nvarchar(MAX) = 'OK';
        BEGIN TRY
UPDATE [dbo].[_ADVANCEPAYMENT] SET _ISDELETED = N'2'
 WHERE _COMPANYID_PK = @_COMPANYID_PK AND _COMPANYDETID_PK = @_COMPANYDETID_PK AND _USERID_PK = @_USERID_PK AND _ADVANCEPAYMENTID_PK = @_ADVANCEPAYMENTID_PK AND _ISDELETED = N'1'
UPDATE [dbo].[_ADVANCEPAYMENTDET]  SET _ISDELETED = N'2'
 WHERE _COMPANYID_PK = @_COMPANYID_PK AND _COMPANYDETID_PK = @_COMPANYDETID_PK AND _USERID_PK = @_USERID_PK AND _ADVANCEPAYMENTID_PK = @_ADVANCEPAYMENTID_PK AND _ISDELETED = N'1'

    END TRY
    BEGIN CATCH
   IF @@TRANCOUNT > 0
        BEGIN
    ROLLBACK TRANSACTION MySavePoint; -- rollback to MySavePoint
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();
    SET @ErrorMessage = 'Error No : '  + CAST ( ERROR_NUMBER() AS nvarchar(MAX)) + CHAR(13) + 'Line No : '  + CAST ( ERROR_LINE() AS nvarchar(MAX))+ CHAR(13) + 'Procedure Name : '  + QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID)) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + 'Error Message : '  + ERROR_MESSAGE();
    RAISERROR( @ErrorMessage, @ErrorSeverity, @ErrorState);
        END
    END CATCH
    COMMIT TRANSACTION 
END; 
SELECT @ErrorMessage 
GO

Answer 5

我不知道您想在那里做什么，但是我可以看到此代码有一些明显的事情，它应该看起来像这样...

注意到您创建过程必须是批处理中唯一的语句，这意味着您不能在一次交易中包装掉落并创建PROC。

IF OBJECT_ID(N'dbo.GET_DATA', N'P') IS NOT NULL
BEGIN 
    DROP PROCEDURE [dbo].[GET_DATA]
END
GO
CREATE PROCEDURE [dbo].[GET_DATA]
    @date datetime2
AS
BEGIN                       --<-- Proc body start
    SET NOCOUNT ON;
BEGIN TRY
    BEGIN TRANSACTION;
            SELECT 
                dbo.Products.product_cod AS 'product_cod',
                dbo.Product_Types.name AS 'product_type_name',
                dbo.UM.name AS 'um_name',
                dbo.Products.category_id AS 'category_id',
                dbo.Bins_Products.bin_id AS 'product_bin_id' 
            FROM dbo.Products
                LEFT JOIN dbo.Product_Types on Products.product_type_id = Product_Types.product_type_id
                LEFT JOIN dbo.UM on Products.um_id = UM.um_id
                LEFT JOIN Bins_Products ON Bins_Products.product_id = Products.product_id
            WHERE
                Products.update_date >= @date;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
      IF (@@TRANCOUNT > 0)   -- Check for open transactions before you try to rollback
      BEGIN
          ROLLBACK TRANSACTION;
      END
    SELECT ERROR_MESSAGE() AS 'ErrorMessage'
END CATCH
END                     --<-- Proc body End 
GO