All or none Transaction

Question

All or none Transaction

scranthdaddy

SSC-Addicted

Points: 492
More actions
May 4, 2003 at 9:05 am

#81477

I am creating a stored proc that will be used to process a group of records, one at a time. The proc will be handed one record at a time and will perform a series of UPDATE, DELETE, and INSERT statements into other relevant tables.
I would like to create transactional integrity by using a transaction that contains all of the 10 INS/UPD/DEL statements. This would be done to ensure the proc didnt fail somewhere in the middle, such as if it errored out on the last statement. I have experimented with @@rowcount and @@error, but was wondering if someone had a good way of doing this.
A generic example of how my proc currently looks is below:
@recvalue1 --this param is passed into the proc
Begin Transaction
UPDATE tbl1 SET @recvalue1 = ...
INSERT INTO tbl2 VALUES ...
DELETE ...
UPDATE tbl4 SET ...
UPDATE tbl5 SET ...
UPDATE tbl6 SET ...
INSERT INTO tbl9 SET ...
End
Thanks!
Andrew J. Hahn

Andrew J. Hahn

Viewing 5 posts - 1 through 4 (of 4 total)

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

jpipes SSChampion Points: 12230 More actions · Answer 1

You're almost there:



CREATE PROCEDURE MyTransProc

   @Param1 DataType

AS



BEGIN TRANSACTION



-- do update/delete



IF @@ERROR <> 0 BEGIN

  ROLLBACK TRANSACTION

  RAISERROR('My error' , 16 , 1)

END



-- Continue update/deletes followed by error handler above



-- Finally:

COMMIT TRANSACTION

Lowell SSC Guru Points: 323506 More actions · Answer 2

I would suggest using XACT_ABORT; it will automatically rollback a transaction, and stop further statements from executing in the transaction when an error is encountered:

SET XACT_ABORT ON

BEGIN TRAN -- with XACT_ABORT, any error automatically rollsback the transaction

DELETE FROM TABLE1

DELETE FROM TABLE2

DELETE FROM TABLE3

COMMIT TRAN -- if we got this far and there were no errors, commit

SET XACT ABORT OFF

lowell@stormrage.com

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!

scranthdaddy SSC-Addicted Points: 492 More actions · Answer 3

scranthdaddy

SSC-Addicted

Points: 492

May 5, 2003 at 3:29 pm

#456214

Thanks guys!

This helps a lot.

Andrew J. Hahn

Brendon Ten Centuries Points: 1252 More actions · Answer 4

What if you're creating dynamic SQL Statements and executing them.

ie. @strSQL = 'UPDATE tablea set column a = 56'

execute sp_executesql @strSQL

Will XACT_ABORT still be effective in this case?