How It Works: TSQL TRY/CATCH Behavior Fooled Me

How It Works: TSQL TRY/CATCH​​ Behavior​​ Fooled Me



If I would have​​ simply​​ read the documentation ( 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




 ​​ ​​ ​​ ​​ ​​ ​​​​ BEGIN​​ TRAN

 ​​ ​​ ​​ ​​ ​​ ​​​​ exec('select * from foo')

if​​ (@@error​​ <>​​ 0)



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.


    exec sp_ExecTest



    select error_number(), error_message(), @@TRANCOUNT as [TRANCOUNT]


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




       exec('select * from foo')


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.

  • I should have read the documentation a more carefully.

  • Make sure you understand the ramifications of using the TSQL TRY / CATCH when making such a call.

  • Bob Dorr