USP_SIMPLEDATALIST_SPONSORSHIPOPPORTUNITYGROUP

create procedure dbo.USP_SIMPLEDATALIST_SPONSORSHIPOPPORTUNITYGROUP (
    @TYPE tinyint = 0,
    @INCLUDEINACTIVE bit = 0,
    @EXCLUDELOCATION uniqueidentifier = null
)as
    set nocount on;

    select 
        ID as VALUE, 
        NAME as LABEL
    from dbo.SPONSORSHIPOPPORTUNITYGROUP
    where (@TYPE = 0 or SPONSORSHIPOPPORTUNITYTYPECODE = @TYPE)
    and (ISINACTIVE = 0 or @INCLUDEINACTIVE = 1)
    and (@EXCLUDELOCATION is null or
         SPONSORSHIPLOCATIONID is null or
         SPONSORSHIPLOCATIONID not in(select WITHIN.ID
                                      from dbo.SPONSORSHIPLOCATION EXCLUDE
                                      inner join dbo.SPONSORSHIPLOCATION WITHIN on WITHIN.HIERARCHYPATH.IsDescendantOf(EXCLUDE.HIERARCHYPATH) = 1
                                      where EXCLUDE.ID = @EXCLUDELOCATION))
    order by SEQUENCE, NAME