Executing batches in a stored proc

Question

Executing batches in a stored proc

dba_user

Default port

Points: 1458
More actions
August 20, 2004 at 12:09 pm

#109039

I have a stored procedure that archives data from a table into a history table. The steps are as follows.

Begin Tran A1
Step One:
Inserts into Table1_HIST (.....) --insert into the history table
Select .... from Table1 where field = 'something'
Step Two:
Delete from Table1 where field = 'something' --delete from the original tables all the records moved to the history table.
If @@Error <> 0 then rollback Tran A1 else commit Tran A1
Is there a way I can make sure that the Insert statement will run and complete before the delete statement runs? If I use 'GO', I get errors in the stored proc. Could the delete statement execute before the insert statement runs causing some records to be deleted before inserted into the _HIST table??

Thanks in advance!!!!

Viewing 7 posts - 1 through 6 (of 6 total)

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

olavho SSChasing Mays Points: 646 More actions · Answer 1

The steps should execute in the order of statements, so (timewise) there is no need to worry that records are deleted before the insertion takes place.

But transaction-wise, another problem may occur. If the first statement (the insert) fails, and the other statement (delete) succeeds, you will not automatically get a rollback. (Unless the setting "XACT_ABORT" is on.)

I normally check after each (significant) statement:

------------------

begin Tran A1

insert into Table1_Hist (...)

if @@error 0 goto proc_exit

select ... from Table1 where field = 'something'

delete from Table1 where field = 'something'

proc_exit:

if @@error 0 rollback Tran A1

else commit Tran A1

------------------

I'm sure there are alternative approaches to do this, and I would be interested in hearing them.

Andy Warren SSC Guru Points: 119902 More actions · Answer 2

Set XACT is brute force, but in many cases perfectly acceptable. Checking @@Error gives you some flexibility, no all errors should cause it to fail.

The downside to the technique you're using is that there is a potential for a record to get changed between the time you archive and delete. In the worst case you would delete a record without archiving it. A better approach is to capture your history in a delete trigger, insuring that deletes can never be missed.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Jeff Moden SSC Guru Points: 1003907 More actions · Answer 3

My suggestions in "bold" typeface...
Step One:
DECLARE A VARIABLE AS AN "INT"
SELECT THE MAX ID FROM THE SOURCE TABLE INTO THE VARIABLE
BEGIN TRAN
Inserts into Table1_HIST (.....) --insert into the history table
Select .... from Table1 WITH (NOLOCK) where field = 'something'
WHERE THE ID IS <= TO THE MAX ID VARIABLE
IF @@ERROR <> 0
BEGIN
PRINT "SOMETHING WENT WRONG WITH THE "INSERT" PORTION OF THE TRASFER"
ROLLBACK
RETURN
END
Step Two:
Delete from Table1 where field = 'something' --delete from the original tables all the records moved to the history table.
WHERE THE ID IS <= TO THE MAX ID VARIABLE
IF @@ERROR <> 0
BEGIN
PRINT "SOMETHING WENT WRONG WITH THE "DELETE" PORTION OF THE TRASFER"
ROLLBACK
RETURN
END
COMMIT
Be advised that if table1 is a transactional input table associated with a GUI, the GUI may be frozen while the DELETE segment is running until COMMIT releases the tables or a ROLLBACK completes. The WITH (NOLOCK) keeps the table from being locked during the SELECT and also allows the INSERT/SELECT to function even if someone does have the table locked (allows "dirty reads").
The purpose of the Max ID variable is to keep from deleting records that may have been inserted to the table during the INSERT/SELECT transfer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dba_user Default port Points: 1458 More actions · Answer 4

Thanks for your responses - it gave me alot to think about.

The records in the table that I'm archiving won't be changed while this procedure is running. My main concern is that I'll delete records before they are inserted into the history table. Some tables in the stored procedure won't be deleted from the table but updated with a new value that is a parameter passed into the stored proc. So, I also worry about the records being udpated before inserted into the history table. So, since the steps execute in the order of statements, I won't need to worry about that right!!???

Thanks for your help!!!

dba_user Default port Points: 1458 More actions · Answer 5

One quick question on the transactions (I'm a newbie), if I do the following:

begin tran A1

insert into table_hist(.....) select ... from tableA where something = 'something'

if @@error <> 0 goto procexit

delete from tableA where something = 'something'

procexit:

if @@error <> 0 goto procexit rollback tran

else

commit TranA1

Will procexit always execute even if I don't call goto procexit?? I want to make sure that the transaction commits.

Thanks, Jill

john-132685 SSCertifiable Points: 6084 More actions · Answer 6

You need to wrap a begin and end around the "then" part of the if statement and put the goto after the rollback . However, the safest method of nesting transactions:

use named save points

BEGIN TRAN

SAVE TRAN xyz

...program logic...

IF @errcondition <> 0 ROLLBACK TRAN xyz

COMMIT TRAN

...this will ensure your transaction nesting level is always correct.