Sql-server – Error on DDL statement never invokes CATCH

ddlsql-server-2008transaction

enter image description here
When I try to close the SS Management Studio window below is the error

I understand that DDL statements are implicit COMMIT and the below WARNING message is because BEGIN TRAN never COMMIT or ROLLBACK. Alter DDL statement aborted and never looped through CATCH block.
Unable to understand why the control is not passed to CATCH block after the error (this does happen with any other DML statement)
Can you please explain why the statements in the CATCH never executed for this DDL error?

Best Answer

Why isn't it caught? Because TRY / CATCH doesn't catch all types of errors. But you can get it to catch the non-system-critical stuff by wrapping it in an EXEC(), such as:

BEGIN TRY
   EXEC('ALTER TABLE...');
END TRY

The error in the ALTER will fail out to the EXEC, which in turn will return to the TRY / CATCH block reporting a simple, catchable error.

For more detail, the MSDN page for TRY...CATCH states:

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.

That second bullet point, about object name resolution errors, is what is causing the ALTER statement to fail and not be eligible to get handled by the CATCH block.

EDIT:
Regarding your statement about "DDL statements are implicit COMMIT": I am not sure what you mean by this exactly, but all (well, nearly all) queries are transactions by themselves. Meaning, single-query batches have no use for explicit BEGIN TRAN / COMMIT / ROLLBACK. If the statement fails it automatically rolls back, and if it succeeds then it automatically commits. You would only use the BEGIN TRAN / COMMIT / ROLLBACK construct if you want to conditionally rollback for some reason. It is still a good idea to use the TRY...CATCH structure to properly handle errors, but you gain nothing (at least with the code posted in the question) with the explicit transaction handling. There is absolutely no difference between the posted code and the following:

BEGIN TRY
   EXEC('ALTER TABLE...');
END TRY
BEGIN CATCH
   SELECT 'I''m in Catch';
END CATCH;
Related Question