Lynn Pettis (8/19/2015)

---

I could the see the problem from the git go:

DECLARE @TableName VARCHAR (50)

DECLARE @BatchSize int

DECLARE @ArchiveYear Date

DECLARE @RowCount int

SET @TableName = 'tblCall'

SET @BatchSize = (SELECT BatchSize FROM PrismDataArchive.dbo.ArchiveDriver WHERE TableName = 'tblCall')

SET @ArchiveYear = (SELECT ArchiveYear FROM PrismDataArchive.dbo.ArchiveDriver WHERE TableName = 'tblCall')

DECLARE @Call_Date Date

SET @Call_Date = (SELECT TOP 1 Call_date

FROM [PrismDataSource].[dbo].[tblCall] tc)

--SELECT * FROM PrismDataArchive.dbo.ArchiveDriver

--WHERE TableName = 'tblCall'

--SELECT @BatchSize, @TableName -- AND (SELECT Active_Indicator FROM PrismDataArchive.dbo.ArchiveDriver) = 1

--WHERE @BatchSize > 0

--SELECT * FROM PrismDataArchive.dbo.ArchiveDriver

DECLARE @Active_Indicator bit

/*

Your problem is here. You are selecting a constant null value (@Active_Indicator is undefined at this point).

SET @Active_Indicator =

(SELECT @Active_Indicator FROM PrismDataArchive.dbo.ArchiveDriver

WHERE Active_Indicator = 1 AND TableName = @TableName)

*/

-- Try this instead:

SELECT @Active_Indicator = Active_Indicator FROM PrismDataArchive.dbo.ArchiveDriver WHERE Active_Indicator = 1 AND TableName = @TableName;

WHILE @BatchSize > 0 AND @TableName = ('tblCall') AND @Active_Indicator = 1

--WHILE @BatchSize > 0 AND @TableName = ('tblCall') AND (SELECT Active_Indicator FROM PrismDataArchive.dbo.ArchiveDriver) = 1

BEGIN

BEGIN TRY

BEGIN TRANSACTION;

IF @Call_Date IS NULL

BEGIN

DELETE TOP (@BatchSize) FROM tc

OUTPUT DELETED.* INTO PrismDataArchive.dbo.tblCallArchive

FROM [PrismDataSource].[dbo].[tblCall] tc

WHERE tc.Call_Date IS NULL

END

ELSE

BEGIN

DELETE TOP (@BatchSize) FROM tc

OUTPUT DELETED.* INTO PrismDataArchive.dbo.tblCallArchive

FROM [PrismDataSource].[dbo].[tblCall] tc

WHERE tc.Call_Date >= DATEADD(yy, DATEDIFF(yy,0,@ArchiveYear), 0) --jan 1 of the year of the date param passed.

AND tc.Call_Date < DATEADD(yy,1,DATEADD(yy, DATEDIFF(yy,0,@ArchiveYear), 0)) --add one year:

END

SET @BatchSize = @@ROWCOUNT; -- capture number of rows affected

IF @BatchSize > 0

BEGIN

INSERT INTO ArchiveLog (TableName,ArchiveYear, BatchSize)

VALUES (@TableName,@ArchiveYear,@BatchSize)

END

--COMMIT TRANSACTION;

END TRY

BEGIN CATCH

--ROLLBACK TRANSACTION;

END CATCH

END

Even with that change, I'm not sure is this going to do what you think it is going to do. I haven't tried to decipher the logic in the code.