Table log Details

IF   EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Logtable]') AND type in (N'U'))
BEGIN
     DROP TABLE [Logtable]
END
GO
IF  NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Logtable]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Logtable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [D] [Nvarchar] (100) NOT NULL,
    [TableName] [Nvarchar](100) NOT NULL,
    [ArchivalTableName] [Nvarchar](100) NOT NULL,
 [StartTime] DateTime  NULL,
 [EndTime] DateTime  NULL,
 [RowCount] BIGINT NULL,
 [Status] [Nvarchar] (50) NULL,
 [Error Message] [Nvarchar](MAX) NULL
 )
END

GO
IF  EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'SPName')
BEGIN
DROP PROCEDURE [dbo].[SPName]
END
GO 
CREATE PROCEDURE [dbo].[SPName]  
@D NVARCHAR(100)  
AS  
  BEGIN  
      SET nocount ON;  
 
      BEGIN try  
   IF  NOT EXISTS (SELECT 1  FROM   [tableName]WHERE [D] = @D)
        BEGIN
           PRINT'Invalid Dataset .Please check provided D '
     RETURN;
     END
          IF Object_id('TempDB.dbo.#PurgeTableList') IS NOT NULL  
            BEGIN  
                DROP TABLE #Ptablelist  
            END  
          --Create Temp table  
          CREATE TABLE #Ptablelist  
            (  
               [id]                [INT] IDENTITY(1, 1) NOT NULL,  
               [D]           [NVARCHAR] (100) NOT NULL,  
               [tablename]         [NVARCHAR](100) NOT NULL,  
               [archivaltablename] [NVARCHAR](100) NOT NULL,  
               [query]        [NVARCHAR](max) NOT NULL  
            )  
         --Load Data to Temp table From  
          INSERT INTO #Ptablelist  
                      ([D],  
                       [tablename],  
                       [archivaltablename],  
                       [query])  
          SELECT DISTINCT [D],  
                          [tablename],  
                          [archivaltablename],  
                          [query]  
          FROM   [tableName]  
          WHERE  [status] = 1  
                 AND [D] = @D  
     ORDER BY [tablename] 
 
 
         --Declare Variable  
          DECLARE @LoopCounter       INT,  
                  @MaxId             INT,  
                  @TableName         NVARCHAR(100),  
                  @ArchivalTableName NVARCHAR(100),  
                  @PurgeLogID        INT,  
                  @PurgeQuery        NVARCHAR(max),  
                  @Count             [BIGINT] = 0  
 
          SELECT @LoopCounter = Min(id),  
                 @MaxId = Max(id)  
          FROM   #Ptablelist  
         --Apply P using While loop 
          WHILE( @LoopCounter IS NOT NULL  
                 AND @LoopCounter <= @MaxId )  
            BEGIN  
                SELECT @PurgeQuery = query,  
                       @TableName = tablename,  
                       @ArchivalTableName = archivaltablename,  
                       @D = D  
                FROM   #Ptablelist  
                WHERE  id = @LoopCounter  
              
                --ADD Purge information to Log table   
               
    Insert INTO [Logtable] ([D],[TableName],[ArchivalTableName],[StartTime],[Status])
                                   Values(@D,@TableName,@ArchivalTableName,Getdate(),'In progress')
                SELECT @PurgeLogID = Scope_identity();  
   
                SET @Count=0
              
               --Execute Delete Query    
                EXEC (@PurgeQuery)  
 
                SET @Count = @@rowcount  
 
                --Update Logtable table with RowCount,EndTime  
                UPDATE [Logtable]  
                SET    [rowcount] = @Count,  
                       [endtime] = Getdate(),
        [status] = 'Completed'
                FROM   [Logtable]  
                WHERE  [id] = @PurgeLogID  
 
               
 
                SET @LoopCounter = @LoopCounter + 1  
            END  
      END try  
 
      BEGIN catch  
   --Add Error information to log table
          UPDATE [Logtable]  
          SET    [rowcount] = @Count,  
                 [endtime] = Getdate(),  
                 [status] = 'Error',  
                 [error message] = Error_message()  
          FROM   [PURGELOGINFO]  
          WHERE  [id] = @PurgeLogID  
 
          RETURN;  
      END catch  
  END
  GO