According to the documentation on TRY...CATCH, I think that your CATCH block should have caught the error.
These are the documented reasons why an error will not be caught in a TRY...CATCH block, none of which appear to apply to your case:
TRY…CATCH constructs do not trap the following conditions:
- Warnings or informational messages that have a severity of
10 or lower.
- Errors that have a severity of
20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.
- Attentions, such as client-interrupt requests or broken client connections.
- When the session is ended by a system administrator by using the
KILL statement.
The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:
- Compile errors, such as syntax errors, that prevent a batch from running.
- Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.
Considering the last 2 reasons in particular, this is not a compilation error, nor is it a deferred name resolution error, because your table name resolves just fine. This really is a runtime error, and should have been caught.
And yet, even though it shouldn't be applicable here, you'll find that the following information provides a working solution for your case:
If an error occurs during compilation or statement-level recompilation at a lower execution level (for example, when executing sp_executesql or a user-defined stored procedure) inside the TRY block, the error occurs at a lower level than the TRY…CATCH construct and will be handled by the associated CATCH block.
Applying the above information, you'll find that you can catch the error by changing your script to either of the following 2 options:
Option 1: sp_executesql
Begin Transaction TRA
Begin Try
exec sp_executesql 'Alter Table history Add Primary Key (employee_id,date_beg)';
End Try
Begin catch
If @@trancount>0
Select ERROR_NUMBER() Error, ERROR_MESSAGE() Mensaje
End Catch
Commit
Option 2: wrapping alter statement in a separate stored procedure
create procedure WrapAlterStatementInSP as
Alter Table history Add Primary Key (employee_id,date_beg);
go
Begin Transaction TRA
Begin Try
exec WrapAlterStatementInSP;
End Try
Begin catch
If @@trancount>0
Select ERROR_NUMBER() Error, ERROR_MESSAGE() Mensaje
End Catch
Commit
Hopefully this helps. But I do feel that there is either a bug in the current behavior, or missing information in the documentation.
