Hi,
i am writing this stored procedure that is going to be scheduled to run as a job. The stored procedure consists of deleting about 600,000 rows each from two tables. The stored procedure will be run on our QA server and then the tables will be migrated to production.
Should I be using transaction commit and rollback in these deletions?
Should each individual row deletion have a transaction of itself? why or why not?
Below is the code with row by row delete inside a transaction..
Your thoughts will be much appreciated.
Zoey

CREATE PROCEDURE DELETEDATA

AS


declare @ID int

/*the temp table will store the id of all the rows that need to be deleted*/
CREATE TABLE #Temp1(ID int PRIMARY KEY)

/*this cursor will allow us to do row by row delete*/
DECLARE CallDetailCursor CURSOR FOR INSERT INTO #Temp1 SELECT CALL_DETAILID FROM CALL_DETAIL
WHERE billing_date <= DATEADD(month,-6,getdate())


FETCH NEXT FROM CallDetailCursor INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN

/*row by row delete*/
BEGIN TRANSACTION DeleteCallDetailRow
DELETE FROM CALL_DETAIL WHERE CALL_DETAILID = @ID
IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
FETCH NEXT FROM CallDetailCursor INTO @ID

END


CLOSE CallDetailCursor
DEALLOCATE CallDetailCursor

/*truncate the table so we can store the id&#39;s of rows from the item summary table*/
truncate table #Temp1

DECLARE ItemSummaryCursor CURSOR FOR INSERT INTO #Temp1 SELECT ItemSummaryID FROM ItemSummary
WHERE billing_date <= DATEADD(month,-6,getdate())


FETCH NEXT FROM ItemSummaryCursor INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN

/*row by row delete*/
DELETE FROM CALL_DETAIL WHERE ItemSummaryID = @ID
FETCH NEXT FROM ItemSummaryCursor INTO @ID

END
CLOSE ItemSummaryCursor
DEALLOCATE ItemSummaryCursor