Hi, I'm confused on how to encapsulate a stored procedure within a transaction.
For example, see the following procedure bit. It calls a number of stored procedures. As it is, the last stored procedure called fails.
BEGIN CALL sp_Trans_InsertRow(128620,15); CALL sp_Trans_InsertRow(76626,115); CALL sp_Trans_InsertRow(76859,25); CALL sp_Trans_InsertRow(3,25); -- This fails END
If use this as is, I get 3 rows inserted. The last one fails as I previously said.
Now, what if I want the whole set to fail when the last one fails, and have NO records inserted???
I tried to use BEGIN TRANSACTION, but I get an error.
BEGIN TRANSACTION; -- This gives an error... CALL sp_Trans_InsertRow(128620,15); CALL sp_Trans_InsertRow(76626,115); CALL sp_Trans_InsertRow(76859,25); CALL sp_Trans_InsertRow(3,25); -- This fails COMMIT;
Could you guys give me some pointers on how this works?
Try adding ATOMIC, as in:
This assumes sp_Trans_InsertRow is not causing commits. All of the calls must succeed, or they will all be undone. You must then commit explicitly after the block completes.
answered 01 Dec '10, 21:59
Another approach is to use savepoints (which are essentially subtransactions).
For your requirements, BEGIN ATOMIC seems easier, but for more complex situations, you can use (usually named) savepoints to make sure parts of a transaction are either completely done or not at all.
Simple (untested!) sample with a named savepoint (which could be omitted here) - it expects that the failing statement signals any kind of exception:
As Bruce states, it is assumed that none of the procs does any commit itself, and it's necessary to do a final commit when the block has finished.
@Bruce: Am I right that ATOMIC blocks are implemented via savepoints internally (in a similar fashion)?
answered 02 Dec '10, 08:41