x

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

Rod gravatar image

Rod
801 47 50 52

(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;
go

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;

    set 
        @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;
go

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

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

The transaction is rolled back due to the above error.

Oleg
more ▼

answered Jul 22, 2011 at 08:40 AM

Oleg gravatar image

Oleg
15.9k 2 4 24

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 ;

BEGIN TRAN ;

BEGIN TRY
    SELECT
        *
    FROM
        sys.columns ;
    -- oh, no! our query failed...
    RAISERROR('gotcha', 25, 1) ;
    COMMIT ;
END TRY
BEGIN CATCH
    SELECT
        *
    FROM
        sys.tables ;
    ROLLBACK ;
END CATCH
more ▼

answered Jul 22, 2011 at 08:43 AM

KenJ gravatar image

KenJ
19.2k 1 3 11

(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

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 75 78 108

@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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1944

asked: Jul 22, 2011 at 07:56 AM

Seen: 1115 times

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