SQL Rollback Script 1

[kav123]

Hi, I will be running an update script on a table on the live database servers. However, this time i thought of writing a script to do the updates, instead of running individual queries. I also want a rollback script. Any ideas how to write a rollback script

 

[bborissov]

Just do all updates in tranasction and if some of them failed then rollback the transaction:

DECLARE @Error int
SET @Error = 0
BEGIN TRANSACTION
  UPDATE ....
  SET @Error = @Error + @@ERROR
  UPDATE ....
  SET @Error = @Error + @@ERROR
  UPDATE ....
  SET @Error = @Error + @@ERROR
  .....
IF @Error <> 0
   ROLLBACK TRANSACTION
ELSE
   COMMIT TRANSACTION

you must store Error code after each statement, because every new line from the script clear the previous error.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[kav123]

Thanks so much for that, that was helpful