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 B

Solution 1:

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() =1779BEGIN--handle expected errors
        PRINT 'Primary Key already exists. ';
    ENDELSEBEGIN--raise other unexpected errors
        THROW;
    END;
END CATCH;

Solution 2:

The first answer is absolutely good, but there is another method to catch error when you use DDL, use stored procedure inside of TRY block. Based on MS documentation.

If you don't want to keep your stored procedure, you can easily drop it on the end of the try.

DROPPROCEDURE IF EXISTS dbo.sp_my_proc
GO
CREATEPROCEDURE dbo.sp_my_proc
AS--Your original code here:ALTERTABLE [dbo].[my_table_name] 
    ADDPRIMARY KEY ([id]);
GO

BEGIN TRY  
    EXECUTE dbo.sp_my_proc 
    --Optional, if you don't want to keep spDROPPROCEDURE IF EXISTS dbo.sp_my_proc  
END TRY  
BEGIN CATCH  
    --Catch your error hereSELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_MESSAGE() AS ErrorMessage;  
END CATCH;

So there is two way to handle this.