CREATE PROCEDURE sw_Purge @counter INT = 1
AS
BEGIN
      SET NOCOUNT ON;

 /*     COMMENT

        This is the method to create the table to work from

        SELECT  subscriber_id,
        CAST(0 AS INT) AS Purged
        INTO PurgedSubs
        FROM subscribers s
        where
            s.act_date is null and
            s.state = 4
                                                END COMMENT */

DECLARE @subID INT
--DECLARE @counter INT = 1

DECLARE purgeCursor CURSOR FOR
SELECT top (SELECT @counter) subscriber_id FROM purgedSubs p WHERE p.purged =0

OPEN purgeCursor

FETCH NEXT FROM purgeCursor INTO @subID


WHILE @@FETCH_STATUS = 0
BEGIN

    BEGIN

    -- Delete all notes
    DELETE FROM subscriber_notes WHERE subscriber_id = @subID

    --Delete all tickets
    DELETE FROM tickets WHERE reference_id = @subID

    --Create an Audit Note
    INSERT INTO subscriber_notes
    (creator_id,date_created,date_last_updated,editor_id,note,note_type,subscriber_id) VALUES
    ( 1,GETDATE(),NULL,NULL,'UK-90807 : User ticket and note data has been purged',0,@subid)

    UPDATE purgedSubs SET purged = 1 WHERE subscriber_id = @subid
    END


FETCH NEXT FROM purgeCursor INTO @subID
END

CLOSE purgeCursor
DEALLOCATE purgeCursor
END