Must a transaction be contained in a TRY...CATCH block?

Can I start a transaction outside of a TRY...CATCH block, and either commit the transaction, or roll it back, within the TRY...CATCH block?

more ▼

asked Jul 22, 2011 at 07:56 AM in Default

avatar image

801 48 51 55

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Yes, you can. Just make sure that you check for @@trancount before attempting to rollback transaction in the catch block. Here is the sample:

 use AdventureWorks;
 set nocount on;
 declare @msg varchar(max);
 declare @i int;
 begin tran;
 begin try
     delete dbo.DatabaseLog
     set @msg = cast(@@rowcount as varchar(10)) + ' records have been deleted.'

     -- force the flow to bail out to the catch block before the damage is done
     set @i = 1 / 0;
     commit tran;
 end try
 begin catch
     if @@trancount > 0 rollback tran;
         @msg = 'Error # ' + cast(error_number() as varchar(10)) + 
         ' occured on line ' + cast(error_line() as varchar(10)) + 
         '. Error message: ' + error_message(); 
 end catch;
 select @msg result;
 -- sanity check (to make sure the records are still there
 select * from dbo.DatabaseLog;
 set nocount off;

The above will produce something like this in the results window:

 Error # 8134 occured on line 15. Error message: Divide by zero error encountered.

The transaction is rolled back due to the above error.


more ▼

answered Jul 22, 2011 at 08:40 AM

avatar image

20.6k 3 7 29

Hmm, I read the question differently from you. I assumed the update TSQL was to be outside the TRY too. May delete my answer if I have misunderstood ... :-/

Jul 22, 2011 at 08:44 AM Fatherjack ♦♦

Of course it goes without saying that try/catch block does not work for Sev 10 errors because it is too mild and of course does not work for Sev 20 cause those are too harsh. The good news is that it is by design and the majority of the standard errors such as constraint violations, invalid PK, dup in the key etc are all Sev 16 :)

Jul 22, 2011 at 08:51 AM Oleg

Although it works, I feel it makes more sense to put all your work inside a try/catch if you are using them. It just seems more sensible to me, if you are doing the whole error management, then use it for everything.

Jul 25, 2011 at 12:36 AM WilliamD

@WilliamD Yes, of course. This type of thinking is caused by the influence of DEV work. For example, if my sample had to be a C# snippet then I could not possibly instantiate a transaction and declare variables in the try block and then expect them to still be visible in the catch because their scope would be limited to try block, so they would be out of scope in the catch :)

Welcome back from your long trip!

Jul 25, 2011 at 08:30 AM Oleg

You can also check the XACT_STATE() to see if the transaction is active and valid

Jul 25, 2011 at 02:59 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

Works like a charm.

Try it out...

 USE tempdb ;
         sys.columns ;
     -- oh, no! our query failed...
     RAISERROR('gotcha', 25, 1) ;
     COMMIT ;
         sys.tables ;
more ▼

answered Jul 22, 2011 at 08:43 AM

avatar image

25k 3 13 20

(comments are locked)
10|1200 characters needed characters left

I dont think that syntax would be supported. Try it on a test environment and see if it works.

What are you trying to achieve? I cant think of a reason you would want to do this. The TRY is where the data update is attempted and then the CATCH is where you handle any issues encountered with completing the data change.

more ▼

answered Jul 22, 2011 at 08:38 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

@Fatherjack It looks like you are thinking about something more complex than what the OP is asking. It is perfectly normal to begin tran and place commit tran in the try block and rollback in the catch (after the @@trancount is checked).

Jul 22, 2011 at 08:42 AM Oleg

Yeah, I think you are right !

Jul 24, 2011 at 11:45 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 22, 2011 at 07:56 AM

Seen: 1474 times

Last Updated: Jul 22, 2011 at 07:56 AM

Copyright 2018 Redgate Software. Privacy Policy