Rollback & Commit transaction - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Rollback & Commit transaction

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-22 : 09:54:45

Urgent writes "If you have Stored Procedure, which has a number of INSERT, UPDATE and etc. batches how I should perform TRANSACTION Error Handling in the right way?
1. EXAMPLE B
BEGIN TRANSACTION A
...
INSERT
...
UPDATE
...
DELETE
....
IF @@ERROR<>0
ROLLBACK TRANSACTION A
ELSE
COMMIT TRANSACTION A
2. EXAMPLE B
BEGIN TRANSACTION A
...
INSERT
...
IF @@ERROR<>0 GOTO ERR
...
UPDATE
...
IF @@ERROR<>0 GOTO ERR
...
DELETE
....
IF @@ERROR<>0 GOTO ERR
...
IF @@ERROR=0 GOTO SUC
ERR:
IF @@ERROR<>0
ROLLBACK TRANSACTION A
SUC:
COMMIT TRANSACTION A
...
Or it might be another way to perform error handling. I will apresiate your help in "Transaction Error Handling".
Thank you in advance."

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-22 : 11:46:50

BEGIN TRANSACTION A
INSERT
UPDATE
DELETE
IF @@ERROR<>0
ROLLBACK TRANSACTION A
ELSE
COMMIT TRANSACTION A

This is incorrect as it does not check for any errors with the update/delete.

Your other example will not work either.
if @@error <> 0 goto err
err:
if @@error <> 0 *

* is checking for any errors in the goto statement so will never rollback. You are also commiting after the rollback
Just take out the check and put the commit before it

IF @@ERROR<>0 GOTO ERR
...
IF @@ERROR<>0 GOTO ERR
GOTO SUC
ERR:
ROLLBACK TRANSACTION A
RETURN
SUC:
COMMIT TRANSACTION A
RETURN

or you could
IF @@ERROR<>0 GOTO ERR
COMMIT TRANSACTION A
RETURN
ERR:
ROLLBACK TRANSACTION A
RETURN

You also might want to save the error code and rowcount
update
select @error = @@error, @rowcount = @@rowcount
if @error <> 0
begin
raiserror('failed update tbl err = %d', 16, -1, @error)
goto err
end

commit tran
return
err:
rollback tran
return

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources