Begin,RollBack,Commit

Question

Begin,RollBack,Commit

Ananth-431739

Ten Centuries

Points: 1185
More actions
January 3, 2008 at 2:38 am

#93877

Hi all,
I'm new to transactions. I'm updating 7 t0 8 tables in an stored procedure
and if any error then transaction should be rollbacked.
create proc name
as
begin
begin tran
update .....
update......
update......
update......
commit tran
if @@error<>0
rollback tran
end
if i execute the stored procedure then the above stored procedure takes more time to execute.
if transactions are used , then the execution time will be huge????
In the stored procedure, cursor and temp table are also used...
Can any one provide me the solution why it is taking more time to execute.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Some comments on that.

You have to check for an error after everty statement. @@Error only refers to the previous statement. If that ocmpleted succcessfully, then @@error = 0, otherwise it has the error code.

You can't rollback after a commit statement. You have to either commit or rollback, depending on the presence of errors.

This is my usual construct in SQL 2000

begin transaction

insert

if @@Error !=0

goto ErrorOccured

update ...

if @@Error !=0

goto ErrorOccured

update ...

if @@Error !=0

goto ErrorOccured

.....

Commit transaction

-- log success

return

ErrorOccured:

rollback transaction

-- do whatever logging is necessary

return

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 2

similarly, there is a SET command which rollsback automatically if an error occurs, so there's no need to explicitly check for @@errors:

create proc name

as

begin

SET XACT_ABORT ON

begin tran

update .....

update......

commit tran

end

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!