USP_INVITATION_DELETE

CREATE procedure dbo.USP_INVITATION_DELETE
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
)
as 
    set nocount on;

    declare @contextCache varbinary(128);

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    /* cache current context information */
    set @contextCache = CONTEXT_INFO();

    /* set CONTEXT_INFO to @CHANGEAGENTID */
    set CONTEXT_INFO @CHANGEAGENTID;

    exec dbo.USP_BUSINESSPROCESS_PARAMETERSETCANBEDELETED 'INVITATIONSTATUS', @ID;

    delete from dbo.INVITEE
    where INVITEE.INVITATIONID = @ID;

    /* reset CONTEXT_INFO to previous value */
    if not @contextCache is null
        set CONTEXT_INFO @contextCache;

    -- Delete the invitation

    exec dbo.USP_INVITATION_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;

    -- Collect all segment ids and idsetregisterids before deleting MKTSegmentation row

    --        MKTSegmentationSegment cascades delete of MKTSegmentation

    declare @SEGMENTS table (SEGMENTID uniqueidentifier, IDSETREGISTERID uniqueidentifier);

    insert into @SEGMENTS
        select
            MKTSEGMENT.ID,
            MKTSEGMENT.IDSETREGISTERID
        from dbo.MKTSEGMENTATIONSEGMENT
        inner join dbo.MKTSEGMENT
            on MKTSEGMENTATIONSEGMENT.SEGMENTID = MKTSEGMENT.ID
        where MKTSEGMENTATIONSEGMENT.SEGMENTATIONID = @ID;

    --Delete the base mailing...

    --    MKTSegmentationSegment rows will cascade

    exec dbo.[USP_MKTSEGMENTATION_DELETE] @ID, @CHANGEAGENTID;

    -- Delete Segments second as otherwise an error will be raised from MKTSegmentationSegment records

    declare @SEGMENTID uniqueidentifier;
    declare @IDSETREGISTERID uniqueidentifier;

    declare SEGMENTCURSOR cursor local fast_forward for
        select
            SEGMENTID,
            IDSETREGISTERID
        from @SEGMENTS;

    open SEGMENTCURSOR;
    fetch next from SEGMENTCURSOR into @SEGMENTID, @IDSETREGISTERID;

    while @@FETCH_STATUS = 0
        begin    
            exec dbo.USP_MKTSEGMENT_DELETEBYID_WITHCHANGEAGENTID @SEGMENTID, @CHANGEAGENTID;
            exec dbo.USP_IDSETREGISTER_DELETEBYID_WITHCHANGEAGENTID @IDSETREGISTERID, @CHANGEAGENTID;

            fetch next from SEGMENTCURSOR into @SEGMENTID, @IDSETREGISTERID;
        end

    close SEGMENTCURSOR;
    deallocate SEGMENTCURSOR;

    return 0;