CODEWITHSUNDEEP
sql-servertry-catch
rodgaldeano
rodgaldeano

Reputation: 35

Try-Catch in SQL Server?

So I recently started with transactions and error handling in SQL Server in college and I've run into this issue which my teachers haven't been able to explain. When I try and execute this block:

Begin Transaction TRA
  Begin Try
    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

I get the following error messages:

Msg 8111, Level 16, State 1, Line 2
Cannot define PRIMARY KEY constraint on nullable column in table 'history'.

Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

So it seems the code doesn't catch the error as it's supposed to.

Would really appreciate it if you could help me!

Upvotes: 3

Views: 14489

Answers (2)

sstan
sstan

Reputation: 36473

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.

Upvotes: 3

Rachel Ambler
Rachel Ambler

Reputation: 1594

As per the BOL, Try Catch will not work when:

an error that occurs during statement-level recompilation will not prevent the batch from compiling, but it will terminate the batch as soon as recompilation for the statement fails. For example, if a batch has two statements and the second statement references a table that does not exist, deferred name resolution causes the batch to compile successfully and start execution without binding the missing table to the query plan until that statement is recompiled. The batch stops running when it gets to the statement that references the missing table and returns an error. This type of error will not be handled by a TRY…CATCH construct at the same level of execution at which the error occurred.

Methinx this is the issue here.

Upvotes: 0

Related Questions