If I would have simply read the documentation (https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-...) the answer to my problem is answered.
Create the following procedure where foo does not exist. Error 208 – Invalid object name occurs when the select is executed.
create procedure sp_ExecTest
as
begin
BEGIN TRAN
exec('select * from foo')
if (@@error <> 0)
rollback
end
Execute the procedure and error 208 is returned as expected.
exec sp_ExecTest
Msg 208, Level 16, State 1, Line 1
Invalid object name 'foo'.
Use TSQL TRY/CATCH around the procedure execution.
BEGIN TRY
exec sp_ExecTest
END TRY
BEGIN CATCH
select error_number(), error_message(), @@TRANCOUNT as [TRANCOUNT]
END CATCH
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.
create procedure sp_ExecTest
as
begin
BEGIN TRAN
exec('select * from foo')
end
Execute the procedure.
exec sp_ExecTest
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.
