CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Rock
Rock

Reputation: 205

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

Upvotes: 1

Views: 696

Answers (2)

kesadae11
kesadae11

Reputation: 298

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.

DROP PROCEDURE IF EXISTS dbo.sp_my_proc
GO
CREATE PROCEDURE dbo.sp_my_proc
AS
    --Your original code here:
    ALTER TABLE [dbo].[my_table_name] 
    ADD PRIMARY KEY ([id]);
GO

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

So there is two way to handle this.

Upvotes: 0

Dan Guzman
Dan Guzman

Reputation: 46203

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;

Upvotes: 5

Related Questions