August 28, 2008 at 8:09 am
Hello all!
I have an SP that is conceptually like this:
begin
declare vars...
begin try
some stuff
if (check)
begin
RaiseError
end
begin transaction
some stuff
....
commit transaction
end try
begin catch
rollback transaction;
someOtherStuff...
end catch;
end
My question is: how do I make the rollback transaction part work even if the error is raised outside of the transaction area? Is there an If Exists Transaction or If Transaction Pending thingy in SQL Server 2005?
Thanks in advance!
Bruno
August 28, 2008 at 1:31 pm
I never had to work with transactions, but we're working with the 'conception'
begin
declare vars...
begin try
some stuff
if (check)
begin
RaiseError
end
ELSE --don't run the transaction unless there's no error
begin transaction
some stuff
....
commit transaction
end try
begin catch
rollback transaction;
someOtherStuff...
end catch;
end
or ma'b you meant something like this
begin
declare vars...
begin try
begin transaction
some stuff
if (check)
begin
RaiseError
end
ELSE --don't run the transaction until it's true
begin transaction
some stuff
....
commit transaction
commit transaction
end try
begin catch
rollback transaction;
someOtherStuff...
end catch;
end
Like I said, I've never had to work with transactions, but I think it's just a 'scope' issue you're working with, so the logic should apply.
August 28, 2008 at 1:58 pm
@@TRANCOUNT will tell you if you have an open transaction (and how many).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 2:17 pm
Thanks!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply