Delete executing twice in while loop

Viewed 36

I am using the following stored procedure to move records in time_elapsed_record_Archive table from the time_elapsed_record table.

ALTER procedure USP_time_elapsed_record_Archive  
AS  
BEGIN  
DECLARE @intialDate datetime = getdate();  
DECLARE @startDate datetime = NULL;  
DECLARE @endDate datetime = NULL;  
DECLARE @count int = 0;  
DECLARE @rowsEffected nvarchar(10);  
select top 1 @startDate = reportdate from time_elapsed_record order by reportdate  
IF(@startDate < DATEADD(YEAR, -2, DATEADD(YY, DATEDIFF(YY,0,@intialDate), 0)))  
BEGIN  
 SELECT @endDate = DATEADD(month, 6, @startDate);  
 IF(@endDate >= DATEADD(YEAR, -2, DATEADD(YY, DATEDIFF(YY,0,@intialDate), 0)))  
 BEGIN  
  SELECT @endDate = DATEADD(YEAR, -2, DATEADD(YY, DATEDIFF(YY,0,@intialDate), -1))  
 END  
END  
ELSE  
BEGIN  
 SET @startDate = null;  
END  
WHILE @startDate <> @endDate   
BEGIN  
 BEGIN TRY  
  IF(@count>0)  
  BEGIN  
   SET @startDate = @endDate;  
   IF(@startDate < DATEADD(YEAR, -2, DATEADD(YY, DATEDIFF(YY,0,@intialDate), 0)))  
   BEGIN  
    SELECT @endDate = DATEADD(month, 6, @startDate);  
    IF(@endDate >= DATEADD(YEAR, -2, DATEADD(YY, DATEDIFF(YY,0,@intialDate), 0)))  
    BEGIN  
     SELECT @endDate = DATEADD(YEAR, -2, DATEADD(YY, DATEDIFF(YY,0,@intialDate), -1))  
    END  
   END  
   ELSE  
   BEGIN  
    SET @startDate = null;  
   END  
  END  
  PRINT CONVERT(nvarchar(10), @startDate, 103)+' start count-'+CONVERT(nvarchar(10), @count);  
  PRINT CONVERT(nvarchar(10), @endDate, 103)+' end count-'+CONVERT(nvarchar(10), @count);  

  INSERT INTO time_elapsed_record_Archive(  
  [employeeid],   
  [reportdate],   
  [reportid],   
  [jobcode],   
  [paycode],   
  [project],   
  [activity],   
  [resource],   
  [resourcecategory],   
  [hours],   
  [status_code],   
  [deletedind],   
  [datecreated],   
  [datemodified],   
  [oprid],   
  [msg_id],   
  [jobrecord],   
  [rate],   
  [currency],   
  [abpayroll],   
  [ismasterjobcode],   
  [isUdcProject],   
  [TimeDrp_personid],   
  [dte_Archived])  
  SELECT [employeeid],   
  [reportdate],   
  [reportid],   
   [jobcode],   
   [paycode],   
   [project],   
   [activity],   
   [resource],   
   [resourcecategory],   
   [hours],   
   [status_code],   
   [deletedind],   
   [datecreated],   
   [datemodified],   
   [oprid],   
   [msg_id],   
   [jobrecord],   
   [rate],   
   [currency],   
   [abpayroll],   
   [ismasterjobcode],   
   [isUdcProject],   
   [TimeDrp_personid],getdate()   
   FROM time_elapsed_record  
  WHERE (reportdate >= @startdate AND reportdate <= @enddate) AND   
  not exists(select reportid   
  from time_elapsed_record_Archive   
  where reportid=time_elapsed_record.reportid AND   
  employeeid = time_elapsed_record.employeeid AND  
  reportdate = time_elapsed_record.reportdate AND  
  paycode = time_elapsed_record.paycode AND  
  employeeid = time_elapsed_record.employeeid AND  
  project = time_elapsed_record.project)  
  
  DELETE FROM time_elapsed_record   
  WHERE (reportdate >= @startdate AND reportdate <= @enddate)    

 END TRY  
  
 BEGIN CATCH  
  INSERT INTO dbo.log4net --INSERTING ErrorInfo INTO LOG TABLE  
  SELECT  
  'Time Elapsed archive package',  
  getdate(),  
  1,  
  'ERROR',  
  'USP_time_elapsed_record_Archive',  
  ERROR_MESSAGE(),  
        (CONVERT(nvarchar(max), ERROR_NUMBER()) + 'ErrorNumber'+  
        CONVERT(nvarchar(max),ERROR_SEVERITY()) + 'ErrorSeverity'+  
        CONVERT(nvarchar(max),ERROR_STATE()) + 'ErrorState'+  
        ERROR_PROCEDURE() + 'ErrorProcedure'+  
        CONVERT(nvarchar(max),ERROR_LINE()) + 'ErrorLine'+  
        ERROR_MESSAGE() + 'ErrorMessage'),  
  'USP_time_elapsed_record_Archive',  
  'SQLSERVER'  
 END CATCH  
 SET @count = @count+1;  
END   
END  

In this SP, I am moving records which are older than 2 years in the archive table. Everything works fine.

Table structures:

CREATE TABLE [dbo].[time_elapsed_record_Archive](
    [archived_id] [int] IDENTITY(1,1) NOT NULL,
    [employeeid] [nvarchar](30) NOT NULL,
    [reportdate] [datetime] NOT NULL,
    [reportid] [nvarchar](30) NOT NULL,
    [jobcode] [nvarchar](30) NOT NULL,
    [paycode] [nvarchar](30) NOT NULL,
    [project] [nvarchar](30) NOT NULL,
    [activity] [nvarchar](30) NOT NULL,
    [resource] [nvarchar](30) NOT NULL,
    [resourcecategory] [nvarchar](30) NOT NULL,
    [hours] [float] NULL,
    [status_code] [int] NULL,
    [deletedind] [bit] NOT NULL,
    [datecreated] [datetime] NULL,
    [datemodified] [datetime] NULL,
    [oprid] [nvarchar](30) NULL,
    [msg_id] [int] NULL,
    [jobrecord] [int] NULL,
    [rate] [decimal](18, 5) NULL,
    [currency] [nvarchar](3) NULL,
    [abpayroll] [bit] NULL,
    [ismasterjobcode] [bit] NULL,
    [isUdcProject] [bit] NOT NULL,
    [TimeDrp_personid] [int] NULL,
    [dte_Archived] [datetime] NOT NULL,
 CONSTRAINT [PK_time_elapsed_record_Archive] PRIMARY KEY CLUSTERED 
(
    [archived_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO



CREATE TABLE [dbo].[time_elapsed_record](
        [employeeid] [nvarchar](30) NOT NULL,
        [reportdate] [datetime] NOT NULL,
        [reportid] [nvarchar](30) NOT NULL,
        [jobcode] [nvarchar](30) NOT NULL,
        [paycode] [nvarchar](30) NOT NULL,
        [project] [nvarchar](30) NOT NULL,
        [activity] [nvarchar](30) NOT NULL,
        [resource] [nvarchar](30) NOT NULL,
        [resourcecategory] [nvarchar](30) NOT NULL,
        [hours] [float] NULL,
        [status_code] [int] NULL,
        [deletedind] [bit] NOT NULL,
        [datecreated] [datetime] NULL,
        [datemodified] [datetime] NULL,
        [oprid] [nvarchar](30) NULL,
        [msg_id] [int] NULL,
        [jobrecord] [int] NULL,
        [rate] [decimal](18, 5) NULL,
        [currency] [nvarchar](3) NULL,
        [abpayroll] [bit] NULL,
        [ismasterjobcode] [bit] NULL,
        [isUdcProject] [bit] NOT NULL,
        [TimeDrp_personid] [int] NULL
    ) ON [PRIMARY]
    GO

PROBLEM: In while loop the delete query is executing twice for each iteration.

enter image description here

Could someone please assist me, how I can resolve this?

0 Answers
Related