USP_DESIGNATIONHYBRID_DELETE

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

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

    declare @LEVEL1ID uniqueidentifier;
    declare @LEVEL2ID uniqueidentifier;
    declare @LEVEL3ID uniqueidentifier;
    declare @LEVEL4ID uniqueidentifier;
    declare @LEVEL5ID uniqueidentifier;
    declare @NUMREVENUERECS int;

    select 
        @LEVEL1ID = DESIGNATIONLEVEL1ID,
        @LEVEL2ID = DESIGNATIONLEVEL2ID,
        @LEVEL3ID = DESIGNATIONLEVEL3ID,
        @LEVEL4ID = DESIGNATIONLEVEL4ID,
        @LEVEL5ID = DESIGNATIONLEVEL5ID
    from 
        dbo.DESIGNATION
    where 
        ID = @ID

    -- get a list of all of the designation levels associated with this hierarchy

    declare @DESIGNATIONLEVELIDS table(DESIGNATIONLEVELID uniqueidentifier);
    insert into @DESIGNATIONLEVELIDS
    select
        case 
            when DESIGNATION.DESIGNATIONLEVEL2ID is null then DESIGNATION.DESIGNATIONLEVEL1ID
            when DESIGNATION.DESIGNATIONLEVEL3ID is null then DESIGNATION.DESIGNATIONLEVEL2ID
            when DESIGNATION.DESIGNATIONLEVEL4ID is null then DESIGNATION.DESIGNATIONLEVEL3ID
            when DESIGNATION.DESIGNATIONLEVEL5ID is null then DESIGNATION.DESIGNATIONLEVEL4ID
            else DESIGNATION.DESIGNATIONLEVEL5ID
        end
    from
        dbo.DESIGNATION
    where
    (
        DESIGNATION.ID = @ID
    )
    or
    (
        (DESIGNATION.DESIGNATIONLEVEL1ID = @LEVEL1ID) and
        (DESIGNATION.DESIGNATIONLEVEL2ID = @LEVEL2ID or @LEVEL2ID is null) and
        (DESIGNATION.DESIGNATIONLEVEL3ID = @LEVEL3ID or @LEVEL3ID is null) and
        (DESIGNATION.DESIGNATIONLEVEL4ID = @LEVEL4ID or @LEVEL4ID is null) and
        (DESIGNATION.DESIGNATIONLEVEL5ID = @LEVEL5ID or @LEVEL5ID is null)
    );

    -- get the hierarchy item if root node

    declare @HIERARCHYID uniqueidentifier;
    select
        @HIERARCHYID = DESIGNATIONLEVELTYPEHIERARCHYITEM.DESIGNATIONLEVELTYPEHIERARCHYID
    from
        dbo.DESIGNATION
    inner join
        dbo.DESIGNATIONLEVELTYPEHIERARCHYITEM on DESIGNATIONLEVELTYPEHIERARCHYITEM.ID = DESIGNATION.DESIGNATIONLEVELTYPEHIERARCHYITEMID
    where
        DESIGNATION.ID = @ID
        and DESIGNATION.DESIGNATIONLEVEL2ID is null;

    declare @CONTEXTCACHE varbinary(128);
    begin try
        -- delete the designation hierarchy

        exec dbo.USP_DESIGNATION_DELETE @ID, @CHANGEAGENTID;

        -- if the designation is root delete hierarchy instance

        exec dbo.USP_DESIGNATIONLEVELTYPEHIERARCHY_DELETEBYID_WITHCHANGEAGENTID @HIERARCHYID, @CHANGEAGENTID;

        -- only delete designation levels which belong exclusively to this hierarchy

        delete from 
            @DESIGNATIONLEVELIDS
        where 
            DESIGNATIONLEVELID in
            (
                select coalesce(DESIGNATIONLEVEL5ID, DESIGNATIONLEVEL4ID, DESIGNATIONLEVEL3ID, DESIGNATIONLEVEL2ID, DESIGNATIONLEVEL1ID) from dbo.DESIGNATION
            );

        set @CONTEXTCACHE = CONTEXT_INFO();
        set CONTEXT_INFO @CHANGEAGENTID;

        -- delete the designation levels

        delete from
            dbo.DESIGNATIONLEVEL
        where
            DESIGNATIONLEVEL.ID in
            (
                select DESIGNATIONLEVELID from @DESIGNATIONLEVELIDS
            );

        if @CONTEXTCACHE is not null
            set CONTEXT_INFO @CONTEXTCACHE;

    end try

    begin catch
        if @CONTEXTCACHE is not null
            set CONTEXT_INFO @CONTEXTCACHE;

        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;
end