How It Works: TSQL TRY/CATCH Behavior Fooled Me

Published Feb 07 2022 07:01 AM 1,068 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.

%3CLINGO-SUB%20id%3D%22lingo-sub-3129182%22%20slang%3D%22en-US%22%3EHow%20It%20Works%3A%20TSQL%20TRY%2FCATCH%20Behavior%20Fooled%20Me%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3129182%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20style%3D%22color%3A%20inherit%3B%20font-family%3A%20inherit%3B%20font-size%3A%2012px%3B%22%3EMove%20from%3A%20bobsql.com%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%20class%3D%22WordSection1%22%3E%0A%3CP%20class%3D%22MsoNormal%22%3EIf%20I%20would%20have%20simply%20read%20the%20documentation%20(%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Flanguage-elements%2Ftry-catch-transact-sql%3Fview%3Dsql-server-ver15%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Flanguage-elements%2Ftry-catch-transact-sql%3Fview%3Dsql-server-ver15%3C%2FA%3E)%20the%20answer%20to%20my%20problem%20is%20answered.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3ECreate%20the%20following%20procedure%20where%20foo%20does%20not%20exist.%26nbsp%3B%20Error%20208%20%E2%80%93%20Invalid%20object%20name%20occurs%20when%20the%20select%20is%20executed.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Ecreate%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Eprocedure%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%20sp_ExecTest%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Eas%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Ebegin%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EBEGIN%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3ETRAN%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Eexec%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20red%3B%22%3E'select%20*%20from%20foo'%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E)%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Eif%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20fuchsia%3B%22%3E%40%40error%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E%26lt%3B%26gt%3B%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%200%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20gray%3B%22%3E)%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20black%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20r%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Eollback%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22text-autospace%3A%20none%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Eend%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CBR%20%2F%3EExecute%20the%20procedure%20and%20error%20208%20is%20returned%20as%20expected.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22line-height%3A%2012.0pt%3B%20background%3A%20%23FFFFFE%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Eexec%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23212121%3B%22%3E%26nbsp%3Bsp_ExecTest%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin-left%3A%201.0in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20color%3A%20red%3B%22%3EMsg%20208%2C%20Level%2016%2C%20State%201%2C%20Line%201%20%3CBR%20%2F%3EInvalid%20object%20name%20'foo'.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin-left%3A%201.0in%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EUse%20TSQL%20TRY%2FCATCH%20around%20the%20procedure%20execution.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22line-height%3A%2012.0pt%3B%20background%3A%20%23FFFFFE%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EBEGIN%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23212121%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3ETRY%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22line-height%3A%2012.0pt%3B%20background%3A%20%23FFFFFE%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23212121%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Eexec%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23212121%3B%22%3E%26nbsp%3Bsp_ExecTest%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22line-height%3A%2012.0pt%3B%20background%3A%20%23FFFFFE%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EEND%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23212121%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3ETRY%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22line-height%3A%2012.0pt%3B%20background%3A%20%23FFFFFE%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EBEGIN%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23212121%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3ECATCH%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22line-height%3A%2012.0pt%3B%20background%3A%20%23FFFFFE%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23212121%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Eselect%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23212121%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23795e26%3B%22%3Eerror_number%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23212121%3B%22%3E()%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23795e26%3B%22%3Eerror_message%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23212121%3B%22%3E()%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23795e26%3B%22%3E%40%40TRANCOUNT%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23212121%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Eas%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23212121%3B%22%3E%26nbsp%3B%5BTRANCOUNT%5D%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22line-height%3A%2012.0pt%3B%20background%3A%20%23FFFFFE%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EEND%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23212121%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3ECATCH%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CBR%20%2F%3EThe%20behavior%20changes%20as%20the%20CATCH%20block%20is%20invoked%20to%20handle%20the%20208%20error%2C%20stopping%20execution%20of%20the%20sp_ExecTest%20at%20the%20exec%20call.%26nbsp%3B%20The%20%40%40error%20and%20rollback%20statements%20are%20no%20longer%20executed%20leaving%20the%20session%20with%20an%20open%20transaction.%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3ENow%20recreate%20the%20procedure%20without%20the%20error%20check%20and%20rollback%20statements.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22line-height%3A%2012.0pt%3B%20background%3A%20%23FFFFFE%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Ecreate%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23212121%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Eprocedure%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23212121%3B%22%3E%26nbsp%3Bsp_ExecTest%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22line-height%3A%2012.0pt%3B%20background%3A%20%23FFFFFE%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Eas%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22line-height%3A%2012.0pt%3B%20background%3A%20%23FFFFFE%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Ebegin%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22line-height%3A%2012.0pt%3B%20background%3A%20%23FFFFFE%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23212121%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3EBEGIN%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23212121%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3ETRAN%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22line-height%3A%2012.0pt%3B%20background%3A%20%23FFFFFE%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23212121%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Eexec%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23212121%3B%22%3E(%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23a31515%3B%22%3E'select%26nbsp%3B*%26nbsp%3Bfrom%26nbsp%3Bfoo'%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23212121%3B%22%3E)%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22line-height%3A%2012.0pt%3B%20background%3A%20%23FFFFFE%3B%20margin%3A%200in%200in%200in%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Eend%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CBR%20%2F%3EExecute%20the%20procedure.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin-left%3A%20.5in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20blue%3B%22%3Eexec%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-size%3A%209.0pt%3B%20font-family%3A%20Consolas%3B%20color%3A%20%23212121%3B%22%3E%26nbsp%3Bsp_ExecTest%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin-left%3A%201.0in%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20color%3A%20red%3B%22%3EMsg%20208%2C%20Level%2016%2C%20State%201%2C%20Line%201%3CBR%20%2F%3EInvalid%20object%20name%20'foo'.%3CBR%20%2F%3EMsg%20266%2C%20Level%2016%2C%20State%202%2C%20Procedure%20sp_ExecTest%2C%20Line%200%3CBR%20%2F%3ETransaction%20count%20after%20EXECUTE%20indicates%20a%20mismatching%20number%20of%20BEGIN%20and%20COMMIT%20statements.%20Previous%20count%20%3D%200%2C%20current%20count%20%3D%201.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin-left%3A%201.0in%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3EWhen%20exiting%20the%20procedure%20if%20the%20transaction%20count%20has%20changed%20the%20266%20error%20is%20raised.%26nbsp%3B%20This%20is%20where%20I%20got%20fooled%20as%20I%20had%20been%20thinking%20that%20error%20266%20would%20occur%20when%20I%20was%20using%20the%20TRY%2FCATCH%2C%20which%20is%20not%20the%20designed%20and%20documented%20behavior.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoListParagraph%22%20style%3D%22text-indent%3A%20-.25in%3B%22%3E1.%3CSPAN%20style%3D%22font%3A%207.0pt%20'Times%20New%20Roman'%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3C%2FSPAN%3EI%20should%20have%20read%20the%20documentation%20a%20more%20carefully.%3C%2FP%3E%0A%3CP%20class%3D%22MsoListParagraph%22%20style%3D%22text-indent%3A%20-.25in%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoListParagraph%22%20style%3D%22text-indent%3A%20-.25in%3B%22%3E2.%3CSPAN%20style%3D%22font%3A%207.0pt%20'Times%20New%20Roman'%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3C%2FSPAN%3EMake%20sure%20you%20understand%20the%20ramifications%20of%20using%20the%20TSQL%20TRY%20%2F%20CATCH%20when%20making%20such%20a%20call.%3C%2FP%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-3129182%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20ins%20and%20outs%20of%20SQL%20Server%20TRY%2FCATCH.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3129182%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBobSQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎Feb 07 2022 07:00 AM
Updated by: