begin try begin tran or begin tran begin try

begin try begin tran or begin tran begin try

Post by SlJTdGVyb » Sat, 29 Sep 2007 02:45:02


Just getting my first crack at 2005's try features, and coded something like
this:

begin tran
begin try
*cause error*
commit tran
end try
begin catch
raiserror(@msg,16,1)
rollback tran
end catch

but it never got to the rollback!

If I changed the order of the begins to:

begin try
begin tran

Then it worked as hoped, it did get to the rollback.

I'm not sure I grok this. I don't see it discussed in BOL. Is this a
documented feature? Is there anything else about it I should realize?

Thanks.

Josh
 
 
 

begin try begin tran or begin tran begin try

Post by Razvan Soc » Sat, 29 Sep 2007 04:12:53


It does get to the rollback. I tried:

begin tran
begin try
select 1/0 infinite
commit tran
end try
begin catch
raiserror('err',16,1)
select @@trancount before
rollback tran
select @@trancount after
end catch

I got:
[...]
before
-----------
1

after
-----------
0

Why do you think that it never gets to the rollback ? Anyway, it seems
more logical to me to use BEGIN TRAN inside the BEGIN TRY block.

--
Razvan Socol
SQL Server MVP

 
 
 

begin try begin tran or begin tran begin try

Post by SlJTdGVyb » Sat, 29 Sep 2007 07:51:00


Well, your example works for me, too.

I tried adding a few more features, and it still works.

My original code was inside a moderately complex SP, and on return from the
SP the @@trancount was 1. This was repeatable.

But I put your code, and several variations, inside of an SP, and it comes
back zero. So I dunno. At least I was not completely crazy to put the begin
tran before the begin try! I guess all I can say is watch out.

Thanks.

Josh