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 (https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver15) 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.

  • 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