Skip to content
Advertisement

Cannot catch SQL Server alter table error

It is a really simple code, but it cannot catch the error.

Or how to catch the error?

Thanks.

BEGIN TRY
    ALTER TABLE [dbo].[my_table_name] 
    ADD PRIMARY KEY ([id]);
END TRY
BEGIN CATCH
    PRINT 'Primary Key already exists. '
END CATCH

Advertisement

Answer

Errors that occur as a result of batch recompilation after deferred name resolution cannot be caught at the same level the error occurs. One work-around is to wrap the DDL in dynamic SQL:

BEGIN TRY
    EXEC(N'ALTER TABLE [dbo].[my_table_name] ADD PRIMARY KEY ([id]);');
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 1779
    BEGIN
        --handle expected errors
        PRINT 'Primary Key already exists. ';
    END
    ELSE
    BEGIN
        --raise other unexpected errors
        THROW;
    END;
END CATCH;
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement