USP_DATALIST_DONATION_DESIGNATION_ANCESTORS

CREATE procedure dbo.USP_DATALIST_DONATION_DESIGNATION_ANCESTORS(@CONTEXTID uniqueidentifier)
as
    set nocount on;

--table of the given designation's ancestor's designation level IDs

with DESIGNATIONLEVEL_CTE (ID, NAME) as (
select DESIGNATIONLEVEL.ID, DESIGNATIONLEVEL.NAME 
from    dbo.DESIGNATION
inner join dbo.DESIGNATIONLEVEL
    on (DESIGNATION.DESIGNATIONLEVEL1ID = DESIGNATIONLEVEL.ID) 
    or (DESIGNATION.DESIGNATIONLEVEL2ID = DESIGNATIONLEVEL.ID) 
    or (DESIGNATION.DESIGNATIONLEVEL3ID = DESIGNATIONLEVEL.ID) 
    or (DESIGNATION.DESIGNATIONLEVEL4ID = DESIGNATIONLEVEL.ID) 
where DESIGNATION.ID = @CONTEXTID
)

select 
    DESIGNATION.ID,
    DESIGNATION.VANITYNAME,
    DESIGNATION.ISACTIVE,
    ISNULL(CAST(dbo.MICROSITEPAGE.SITEPAGESID as bit), 0) as ISCMS,
    case
        when (ISNULL(CAST(dbo.MICROSITEPAGE.SITEPAGESID as bit), 0) = 1)
            and (DESIGNATION.ISACTIVE = 1)
            and (((GETDATE() >= DESIGNATION.STARTDATE) or (DESIGNATION.STARTDATE is null)) and ((GETDATE() <= DESIGNATION.ENDDATE) or (DESIGNATION.ENDDATE is null)))
            then 1
        else 0
    end as ISLIVE,
    isnull([DESIGNATION_MICROSITEEMAILTEMPLATE].[EMAILTEMPLATEID], 0) as ACKNOWLEDGEMENTEMAILTEMPLATEID
from DESIGNATION 
inner join DESIGNATIONLEVEL_CTE as DL
    on ((DESIGNATION.DESIGNATIONLEVEL1ID = DL.ID) and (DESIGNATION.DESIGNATIONLEVEL2ID is null))
    or ((DESIGNATION.DESIGNATIONLEVEL2ID = DL.ID) and (DESIGNATION.DESIGNATIONLEVEL3ID is null))
    or ((DESIGNATION.DESIGNATIONLEVEL3ID = DL.ID) and (DESIGNATION.DESIGNATIONLEVEL4ID is null))
    or ((DESIGNATION.DESIGNATIONLEVEL4ID = DL.ID) and (DESIGNATION.DESIGNATIONLEVEL5ID is null))
    or ((DESIGNATION.DESIGNATIONLEVEL5ID = DL.ID))
left join dbo.MICROSITEPAGE
    on (dbo.MICROSITEPAGE.OBJECTID = DESIGNATION.ID) and (dbo.MICROSITEPAGE.EXCLUDED = 0)
left join dbo.[DESIGNATION_MICROSITEEMAILTEMPLATE] on
    ([DESIGNATION].[ID] = [DESIGNATION_MICROSITEEMAILTEMPLATE].[DESIGNATIONID]) and ([DESIGNATION_MICROSITEEMAILTEMPLATE].[ACTIVE] = 1)
order by 
    case
        when DESIGNATION.DESIGNATIONLEVEL2ID is null then 1
        when DESIGNATION.DESIGNATIONLEVEL3ID is null then 2
        when DESIGNATION.DESIGNATIONLEVEL4ID is null then 3
        when DESIGNATION.DESIGNATIONLEVEL5ID is null then 4
        else 5
    end        
desc