Rollback group of DDL statements

Working in SQL Server 2008 R2, I am trying to rollback a set of DDL statements as a group (think of an upgrade script for a database), but am running into trouble.

Take the following code:

begin try

begin tran

create table foo (i int)
alter table foo add x dog
insert into foo select 1,1
insert into foo select 1,1,1

commit tran

end try

begin catch

rollback tran
print @@error

end catch

I'm expecting the try to fail on the alter table statement, drop to the catch, rollback the transaction, and print the error message. However, if you check your objects/tables, you'll see that foo is still there (so the create table didn't rollback properly).

select * from sys.objects where name = 'foo'

What am I doing wrong here?

The error is happening because the error being thrown part of a recompile error due to deferred name resolution. Looking at SQL BOL those aren't trapped when they happen at the same level as the try...catch. However, if it's happening at a different level, either as dynamic SQL or a SP call, then it will get caught and rolled back.

Using Profiler you can see that the "alter table foo add x dog" statement recompiles before executing and then errors and bypasses the catch block.

If you wrap the statements in dynamic SQL the error isn't returned to Profiler and the transaction is rolled back

StackExchange Database Administrators Q#29544, answer score: 5