The behavior changes as the CATCH block is invoked to handle the 208 error, stopping execution of the sp_ExecTest at the exec call. The @@error and rollback statements are no longer executed leaving the session with an open transaction.
Now recreate the procedure without the error check and rollback statements.
exec('select * from foo')
Execute the procedure.
Msg 208, Level 16, State 1, Line 1 Invalid object name 'foo'. Msg 266, Level 16, State 2, Procedure sp_ExecTest, Line 0 Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
When exiting the procedure if the transaction count has changed the 266 error is raised. This is where I got fooled as I had been thinking that error 266 would occur when I was using the TRY/CATCH, which is not the designed and documented behavior.
1.I should have read the documentation a more carefully.
2.Make sure you understand the ramifications of using the TSQL TRY / CATCH when making such a call.