TRY/CATCH 中 DROP 约束的语法错误

Question

我有以下T-SQL：

USE [MYDB]
GO
SET XACT_ABORT, NOCOUNT ON
GO
BEGIN TRANSACTION;
BEGIN TRY
        ALTER TABLE [dbo].[ContactRole] 
        DROP CONSTRAINT [FK_8bff7074914bc29885004c0a323] 
        GO
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage;
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

解析时会给我这个错误：

Msg 102，级别 15，状态 1，第 9
行 "FK_8bff7074914bc29885004c0a323"附近的语法不正确。

单独或仅在事务中时，没有解析错误。

Answer 1

将GO放在ALTER TABLE ... DROP CONSTRAINT ..行之后

....
BEGIN TRANSACTION;
BEGIN TRY
    ALTER TABLE [dbo].[ContactRole] 
    DROP CONSTRAINT [FK_8bff7074914bc29885004c0a323] 
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage;
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH

GO不是 SQL 关键字 - SQL Server Management Studio 仅将其理解为"批处理分隔符"。

另外：将COMMIT TRANSACTION语句放在 ALTER TABLE 命令之后 - 毕竟，如果出现问题 - 代码执行将立即跳入 catch 块，因此只有在一切正常的情况下才会执行该行

Answer 2

你不应该有Go，因为它指示批处理的结束，你的提交应该紧跟在改变表之后。

也没有必要检查事务是否在 catch 上运行，因为在这种情况下，它将始终具有

USE [MYDB]
GO
SET XACT_ABORT, NOCOUNT ON
GO
BEGIN TRANSACTION;
BEGIN TRY
        ALTER TABLE [dbo].[ContactRole] 
        DROP CONSTRAINT [FK_8bff7074914bc29885004c0a323] 
        COMMIT TRANSACTION; 
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage;
        ROLLBACK TRANSACTION;
END CATCH