Purge sp to print deleted count

/****************************************************************************************************************
-- SQL Script    :  ES_Purge_ContactBase.sql
--****************************************************************************************************************
-- Description    :
--------Delete all contact records in the ContactBase table that are not associated with an active policy or quote 
--------and no associated with any customer validation and interaction note data already stored in CRM

--------Inactive contacts are defined as contact records satisfying all of the following conditions:
--------1.    Contacts not associated with any active policy (eas_policy)  
--------2.    Contacts not associated with any active quote (eas_quote)  
--------3.    Contact records that are not associated with any records in the following tables:
--------a.    eas_customervalidationBase
--------b.    eas_interactionBase

--------The physical delete needs to be cascaded to the following entities based on the foreign key constraints.
--------1.    ContactBase
--------2.    eas_policybase
--------3.    eas_quotebase
--------4.    eas_policycontactbase
--------5.    eas_contact_policy_relationship
--------6.    eas_quotecontactbase
--------7.    eas_contact_quote_relationship
--------8.    CustomerAddressBase

---PID          : 15-CLM-174
-- Developed    : Babji Reddy
--created_date  : 03/14/2016
-- Reviewed by  :
-- Modified By    :
-- Release        : 16-REL-03
--********************************************************************************************************************/

--USE ENV_CRM
--GO

IF EXISTS(SELECT * FROM sys.objects WHERE object_id=object_id(N'ES_Purge_ContactBase') AND TYPE IN(N'P',N'PC'))
BEGIN
    DROP PROC dbo.ES_Purge_ContactBase
END
GO
   
CREATE PROCEDURE dbo.ES_Purge_ContactBase
(
  @number_of_purgerecords INT=NULL
)
AS
BEGIN
    BEGIN TRY;
    SET NOCOUNT ON;
    DECLARE @message VARCHAR(MAX)=''
    DECLARE @CustomerAddressBase INT=0
    DECLARE @eas_contact_quote_relationshipBase INT=0
    DECLARE @eas_quotecontactBase INT=0
    DECLARE @eas_quoteBase INT=0
    DECLARE @eas_policycontactBase INT=0
    DECLARE @eas_contact_policy_relationshipBase INT=0
    DECLARE @eas_policyBase INT=0 
    DECLARE @contactbase INT=0

    IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'Temp_ContactBase_main') AND OBJECTPROPERTY(id,N'IsUserTable')=1)
    BEGIN
        DROP TABLE dbo.Temp_ContactBase_main
    END
           
    IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'Temp_ContactBase_sub') AND OBJECTPROPERTY(id,N'ISUserTable')=1)
    BEGIN
        DROP TABLE dbo.Temp_ContactBase_sub
    END

    CREATE TABLE dbo.Temp_ContactBase_main
        (
            ContactId uniqueidentifier NOT NULL,
            CONSTRAINT PK_Temp_ContactBase_main PRIMARY KEY CLUSTERED (ContactId)               
        )

           
    CREATE TABLE Temp_ContactBase_sub
    (
        ContactId uniqueidentifier NOT NULL, 
        CONSTRAINT PK_Temp_ContactBase_sub PRIMARY KEY CLUSTERED (ContactId)
    )
           
IF @number_of_purgerecords IS  NULL
BEGIN 
;WITH Un_wanted_contacts AS(
SELECT eas_Contact AS ContactId from eas_quotecontactbase  (NOLOCK)  where statecode=0 
UNION 
 SELECT eas_contactid AS ContactId FROM eas_policycontactbase (NOLOCk) WHERE StateCode=0
UNION 
 SELECT eas_contactid  AS ContactId FROM  eas_interactionBase (NOLOCk) 
UNION 
 SELECt eas_contactid  AS ContactId FROM  eas_customervalidationBase (NOLOCk)
UNION 
 SELECt eas_PolicyHolderNameId  AS ContactId FROM  eas_customervalidationBase (NOLOCk)
 UNION
SELECT eas_primaryinsured  FROM eas_quotebase (NOLOCK) WHERE statecode = 0
 UNION
 SELECT eas_policyholderid FROM dbo.eas_policybase (NOLOCK) WHERE statecode = 0
)
INSERT INTO Temp_ContactBase_main(ContactId)
SELECT ContactId FROM contactbase  (NOLOCK) 
 EXCEPT 
 SELECT ContactId FROM Un_wanted_contacts

 END 
    ELSE 
    BEGIN

        ;WITH Un_wanted_contacts AS(
         SELECT eas_Contact AS ContactId from eas_quotecontactbase  (NOLOCK)  where statecode=0 
         UNION 
         SELECT eas_contactid AS ContactId FROM eas_policycontactbase (NOLOCk) WHERE StateCode=0
         UNION 
         SELECT eas_contactid  AS ContactId FROM  eas_interactionBase (NOLOCk) 
         UNION 
         SELECt eas_contactid  AS ContactId FROM  eas_customervalidationBase (NOLOCk)
         UNION 
         SELECt eas_PolicyHolderNameId  AS ContactId FROM  eas_customervalidationBase (NOLOCk)
         UNION
         SELECT eas_primaryinsured  FROM eas_quotebase (NOLOCK) WHERE statecode = 0
         UNION
         SELECT eas_policyholderid FROM dbo.eas_policybase (NOLOCK) WHERE statecode = 0
        )
        INSERT INTO Temp_ContactBase_main(ContactId)
        SELECT top (@number_of_purgerecords) ContactId FROM (
        SELECT ContactId FROM contactbase  (NOLOCK) 
         EXCEPT 
         SELECT ContactId FROM Un_wanted_contacts) a
 
   END    
                              
INSERT INTO dbo.Temp_ContactBase_sub(ContactId)
SELECT TOP 10000 ContactId FROM Temp_ContactBase_main WITH(NOLOCK)


WHILE EXISTS( SELECT 1 FROM Temp_ContactBase_main)
BEGIN   
         
        --CustomerAddressBase 
        --eas_contact_quote_relationship 
        --eas_quotecontactbase 
        --eas_quotebase 
        --eas_policybase 
        --eas_policycontactbase 
        --eas_contact_policy_relationship 
        --contactBase

        SET @message=''

        ---CustomerAddressBase

        DELETE cab
        FROM dbo.CustomerAddressBase cab
        INNER JOIN dbo.Temp_ContactBase_sub cb WITH(NOLOCk)
        ON cab.ParentId=cb.ContactId
       
        SET @CustomerAddressBase=@CustomerAddressBase+@@ROWCOUNT
        SET @message=@message+'No of purge records in CustomerAddressBase:'+CAST(@CustomerAddressBase AS VARCHAR(10))
       
        -- eas_contact_quote_relationshipBase
        DELETE ecqr
        FROM  dbo.eas_contact_quote_relationshipBase ecqr
        INNER  JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
        ON ecqr.ContactId=re_sub.ContactId

        SET @eas_contact_quote_relationshipBase=@eas_contact_quote_relationshipBase+@@ROWCOUNT   
        SET @message=@message+SPACE(2)+'Number of purge records in eas_contact_quote_relationshipBase:'+CAST(@eas_contact_quote_relationshipBase AS VARCHAR(10)) 

        --eas_quotecontactBase
        DELETE ecqr
        FROM  dbo.eas_quotecontactBase ecqr
        INNER  JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
        ON ecqr.eas_Contact=re_sub.ContactId

        SET @eas_quotecontactBase=@eas_quotecontactBase+@@ROWCOUNT
        SET @message=@message+SPACE(2)+'Number of purge records in eas_quotecontactBase:'+CAST(@eas_quotecontactBase AS VARCHAR(10)) 

        --eas_quoteBase
        DELETE ecqr
        FROM  dbo.eas_quoteBase ecqr
        INNER  JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
        ON ecqr.eas_primaryinsured=re_sub.ContactId

        SET @eas_quoteBase=@eas_quoteBase+@@ROWCOUNT
        SET @message=@message+SPACE(2)+'Number of purge records in eas_quoteBase:'+CAST(@eas_quoteBase AS VARCHAR(10)) 
       
       --eas_policycontactBase
            DELETE ecv
         FROm dbo.eas_policycontactBase ecv       
         INNER  JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
         ON ecv.eas_contactid=re_sub.ContactId

        SET @eas_policycontactBase=@eas_policycontactBase+@@ROWCOUNT
        SET @message=@message+SPACE(2)+'Number of purge records in eas_policycontactBase:'+CAST(@eas_policycontactBase AS VARCHAR(10)) 


      ---eas_contact_policy_relationship
        DELETE ecpr
        FROM  dbo.eas_contact_policy_relationshipBase ecpr       
        INNER  JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
        ON ecpr.contactid=re_sub.ContactId         

         SET @eas_contact_policy_relationshipBase=@eas_contact_policy_relationshipBase+@@ROWCOUNT
         SET @message=@message+SPACE(2)+'Number of purge records in eas_contact_policy_relationshipBase:'+CAST(@eas_contact_policy_relationshipBase AS VARCHAR(10)) 
       ---eas_policyBase
        DELETE ecqr
        FROM  dbo.eas_policyBase ecqr
        INNER  JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
        ON ecqr.eas_PolicyHolderId=re_sub.ContactId
        
        SET @eas_policyBase=@eas_policyBase+@@ROWCOUNT
        SET @message=@message+SPACE(2)+'Number of purge records in eas_contact_policy_relationshipBase:'+CAST(@eas_policyBase AS VARCHAR(10)) 
        --- Contactbase
        DELETE ab
        FROM  dbo.ContactBase ab 
        INNER  JOIN Temp_ContactBase_sub re_sub WITH(NOLOCK)
        ON ab.ContactId=re_sub.ContactId
       
        SET @ContactBase=@ContactBase+@@ROWCOUNT
        SET @message=@message+SPACE(2)+'Number Of Purge Records in ContactBase:'+CAST(@ContactBase AS VARCHAR(10))

        DELETE ab
        FROM  Temp_ContactBase_main ab
        INNER JOIN  Temp_ContactBase_sub y WITH(NOLOCK)
        On ab.ContactId=y.ContactId

        TRUNCATE TABLE Temp_ContactBase_sub
       
        INSERT INTO  Temp_ContactBase_sub(ContactId)
        SELECT TOP 10000 ContactId FROM Temp_ContactBase_main WITH(NOLOCK)
           
END

INSERT INTO CRM_Staging_Data.dbo.CRM_Purge_Log(Script_Name,Purge_Date,Purge_user,[Message])
SELECT 'SP: ES_Purge_ContactBase',GETDATE(),USER_NAME(),@message

    IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'Temp_ContactBase_main') AND OBJECTPROPERTY(id,N'IsUserTable')=1)
    BEGIN
        DROP TABLE Temp_ContactBase_main
    END
   
    IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'Temp_ContactBase_sub') AND OBJECTPROPERTY(id,N'IsUserTable')=1)
    BEGIN
        DROP TABLE Temp_ContactBase_sub   
    END
       
END TRY  
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
   
    SELECT  @ErrorMessage = N'Error in SP: dbo.ES_Purge_ContactBase: ' + ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();
    INSERT INTO CRM_Staging_Data.dbo.CRM_Purge_Log(Script_Name,Purge_Date,Purge_user,[Message])
    SELECT 'SP: ES_Purge_ContactBase',GETDATE(),USER_NAME(),@message+@ErrorMessage
    RAISERROR( @ErrorMessage,@ErrorSeverity,@ErrorState );
END CATCH
END