1

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rock
  • 205
  • 1
  • 4
  • 14
  • You might find this useful: http://www.sqlusa.com/articles2008/trycatch/. – Gordon Linoff Dec 03 '15 at 03:35
  • Maybe you can verify if already exists... IF NOT EXITS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY' ....blah – Mate Dec 03 '15 at 03:36
  • 1
    IF NOT exists (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'my_table_name') ALTER TABLE [dbo].[my_table_name] ADD PRIMARY KEY ([id]); – Rock Dec 03 '15 at 04:08
  • Thanks, it also works. – Rock Dec 03 '15 at 04:09
  • Great "Most languages have existing control structures designed to solve your problems without the use of exceptions" Don't fall into antipatterns – Mate Dec 03 '15 at 05:03

2 Answers2

5

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;
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
0

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.

kesadae11
  • 178
  • 2
  • 11