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;