How It Works: TSQL TRY/CATCH Behavior Fooled Me

Published Feb 07 2022 07:01 AM 1,129 Views
Microsoft

Move from: bobsql.com

 

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.

Co-Authors
Version history
Last update:
‎Feb 07 2022 07:00 AM
Updated by: