Stored Proc Archives rows to another table

-- =============================================
-- Author:Farrell Thomas
-- Create date: 3/16/2017
-- Description:This will archive rows from one table to another table
-- =============================================


-- sample usage
-- EXEC usp_archive_rows  @sourceTABLENAME = '[ftest].[dbo].[Table_1]',    --[database].[schema].[tablename] format
                         --@desttablename = '[ftest_log].[dbo].[arch_table_1]', 
 --@column_to_check = 'logdate',
 --@PURGE_DATE = '2014-04-01 00:00:00.000', 
 --@BATCH_SIZE = 500, 
 --@MINUTES_TO_PROCESS = 5

 
CREATE PROCEDURE [dbo].[usp_archive_rows]

@sourcetablename varchar(200) ,                                  -- source table to purge
@desttablename   varchar(200),                                   -- dest table
@column_to_check varchar(200),                                   -- date column name to check in source table
@purge_date varchar(50)  = '1990-01-01 00:00:00.000',            -- default to 1990 in case no date was specified than nothing should be purged 
@batch_size int = 50 ,                                           -- default to 50 for batch size
@minutes_to_process int = 5                                      -- default to 5 minutes to run
AS
BEGIN

DECLARE @SQLSTR NVARCHAR(500)
DECLARE @KEEP_PROCESSING INT = 0
DEClARE @STARTTIME DATETIME = GETDATE()
DECLARE @NUMMINUTES INTEGER
DECLARE @CURRTIME DATETIME = GETDATE()
DECLARE @TABLEROWS INTEGER = 0
DECLARE @TOTALDEL INTEGER = 0

--- count number of rows to delete

SET @SQLSTR = 'Set @Dynamicint = (select count(*) from ' + @sourcetablename + ' where ' + @column_to_check + ' < ' + '''' + @purge_date + '''' + ')'

EXEC sp_executesql
    @SQLSTR
    ,N'@Dynamicint int OUTPUT'
    ,@TABLEROWS OUTPUT

SET ROWCOUNT @batch_size       -- set the rowcount to the passed batch size
SET NOCOUNT ON                 -- DONT DISPLAY NUMBER OF ROWS MESSAGES

PRINT 'Source Table : ' + @sourcetablename
PRINT 'Destination  : ' + @desttablename
PRINT 'Purge Date   : ' + @purge_date + ' <-- rows with ' + @column_to_check + ' less than this will be moved and purged'

PRINT 'TOTAL ROWS TO PROCESS = ' + CONVERT(VARCHAR(10),@TABLEROWS)

------this will create the destination table if it dosent exist

IF OBJECT_ID(@desttablename, N'U') IS NULL
BEGIN
  SET @SQLSTR = 'Select * into ' + @desttablename + ' from ' + @sourcetablename + ' where 1 = 0'
  EXEC (@sqlstr)
  PRINT 'Created destination Table : ' + @desttablename
END

-- Construct sql delete statement
       
   SET @SQLSTR = 'DELETE ' + @sourcetablename + ' OUTPUT DELETED.* INTO ' + @desttablename + ' where ' + @column_to_check + ' < ' + '''' + @purge_date + ''''

-----------------------------------
--- main loop
-----------------------------------

--PROCESS THE ROWS WHILE KEEP_PROCESSING = 0

WHILE @KEEP_PROCESSING = 0

BEGIN

       BEGIN TRANSACTION

   EXEC(@sqlstr)   -- execute the delete statement

   SET @TOTALDEL = @TOTALDEL + @@ROWCOUNT

       commit

PRINT 'Total Processed so far = ' + CONVERT(VARCHAR(10),@TOTALDEL)

--check if all rows processed

    IF  @TOTALDEL >= @TABLEROWS 
      BEGIN
          PRINT 'All Rows Processed - EXITING JOB'
  PRINT 'TOTAL ROWS DELETED = ' + CONVERT(VARCHAR(10),@TOTALDEL)
          SET @KEEP_PROCESSING = 1

  -- commit last transaction if still open

  if @@TRANCOUNT > 0
  begin
    commit
  end

      END

   ---- check if number of minutes to run has expired.

   SET @CURRTIME = GETDATE()
   SET @NUMMINUTES =  datediff(MINUTE, @STARTTIME,@CURRTIME)
    
   IF  @NUMMINUTES > @minutes_to_process
   BEGIN
          PRINT 'RUN TIME EXCEDDED - EXITING JOB'
  PRINT 'TOTAL ROWS DELETED = ' + CONVERT(VARCHAR(10),@TOTALDEL)
          SET @KEEP_PROCESSING = 1

   -- commit last transaction if still open

  if @@TRANCOUNT > 0
  begin
    commit
  end
   END

---------------------------------------------
---  end main loop
---------------------------------------------

END



END