CODEWITHSUNDEEP
sql-server-2008t-sql
Steve Mallory
Steve Mallory

Reputation: 4283

Syntax error with DROP CONSTRAINT within TRY/CATCH

I have the following 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;

Which gives me this error when parsed:

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'FK_8bff7074914bc29885004c0a323'.

When alone, or just within a transaction, there is no parse error.

Upvotes: 0

Views: 730

Answers (2)

Diego
Diego

Reputation: 36146

you shouldn't have the Go because it indicates the end of the batch and your commit should be right after the alter table.

Also there is no need to check if a transaction is running on the catch because on this situation, it will always have

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

Upvotes: 0

marc_s
marc_s

Reputation: 754518

Drop the GO after the ALTER TABLE ... DROP CONSTRAINT .. line

....
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 is not a SQL keyword - it's only understood by SQL Server Management Studio as a "batch separator".

Also: put your COMMIT TRANSACTION statement right after the ALTER TABLE command - after all, if something does go wrong - the code execution will jump into the catch block right away, so that line is only executed if everything went OK

Upvotes: 1

Related Questions