USP_DATALIST_MICROSITEMEMBERSHIPPROGRAMS

            CREATE procedure dbo.USP_DATALIST_MICROSITEMEMBERSHIPPROGRAMS
                (
                    @LIVEONLY bit = null,
                    @NAMELIKE nvarchar(512) = null
                ) as
                    set nocount on;

                    declare @BBNCURL nvarchar(1024) = dbo.UFN_BBNC_URL();

                    with MEMBERSHIPPROGRAM_CTE as (
                        select 
                            MEMBERSHIPPROGRAM.ID,
                            MEMBERSHIPPROGRAM.NAME,
                            MEMBERSHIPPROGRAM.DESCRIPTION,
                            ISNULL(CAST(dbo.MICROSITEPAGE.SITEPAGESID AS BIT), 0) AS ISBASICCMS,
                            [LIVESTATUS].[ISLIVE],
                            MICROSITEPAGE.SITEPAGESID AS SITEPAGESID,
                            CASE WHEN ISNULL(CAST(MICROSITEPAGE.SITEPAGESID AS BIT), 0) = 1 then @BBNCURL + (SELECT TOP 1 VanityURL from dbo.VanityURL where PageID= MICROSITEPAGE.SITEPAGESID) ELSE '' END AS URL,
                            MICROSITEPAGE.PRIMARYCONTENTID AS PRIMARYCONTENTID,
                            [NAMEMATCH].[ISMATCH] as [NAMEISMATCH],
                            [MEMBERSHIPPROGRAM].[ISACTIVE],
                            MEMBERSHIPPROGRAM.PROGRAMTYPECODE,
                            MEMBERSHIPPROGRAM.PROGRAMBASEDONCODE,
                            case when (MEMBERSHIPPROGRAM.PROGRAMTYPECODE = 0 and MEMBERSHIPPROGRAM.PROGRAMBASEDONCODE = 0) then 1
                            else 0
                            end as ISANNUALDUESBASED,
                            MEMBERSHIPPROGRAM_MICROSITEEMAILTEMPLATE.ID as MEMBERSHIPPROGRAM_MICROSITEEMAILTEMPLATEID,
                            coalesce(MEMBERSHIPPROGRAM_MICROSITEEMAILTEMPLATE.ACTIVE, 0) as HASACTIVEACKNOWLEDGEMENTEMAIL
                        from dbo.MEMBERSHIPPROGRAM
                        left join dbo.MICROSITEPAGE
                            on (MICROSITEPAGE.OBJECTID = MEMBERSHIPPROGRAM.ID) and (MICROSITEPAGE.EXCLUDED = 0)
                        left outer join dbo.MEMBERSHIPPROGRAM_MICROSITEEMAILTEMPLATE
                            on MEMBERSHIPPROGRAM.ID = MEMBERSHIPPROGRAM_MICROSITEEMAILTEMPLATE.MEMBERSHIPPROGRAMID
                        cross apply (
                            select case
                                    when (ISNULL(CAST(MICROSITEPAGE.SITEPAGESID AS BIT), 0) = 1) and (MEMBERSHIPPROGRAM.ISACTIVE = 1)
                                        then 1
                                    else 0
                            end AS ISLIVE
                        ) as [LIVESTATUS]
                        cross apply (
                            select case
                                when (coalesce(@NAMELIKE, '%%') = '%%') then 1
                                when (MEMBERSHIPPROGRAM.NAME like '%' + @NAMELIKE + '%') then 1
                                else 0
                            end as [ISMATCH]
                        ) as [NAMEMATCH]
                        where 
                            ((coalesce(@LIVEONLY, 0) = 0) or [LIVESTATUS].[ISLIVE] = 1) 
                    ),
                    MEMBERSHIPLEVEL_CTE as (
                        select
                            MEMBERSHIPLEVEL.ID,
                            MEMBERSHIPLEVEL.NAME,
                            MEMBERSHIPLEVEL.DESCRIPTION,
                            case when MEMBERSHIPPROGRAM.[ISBASICCMS] = 1 and [MICROSITEMEMBERSHIPLEVEL].[ID] is not null then 1 else 0 end as [ISBASICCMS],
                            [LIVESTATUS].[ISLIVE],
                            MEMBERSHIPPROGRAM.SITEPAGESID,
                            MEMBERSHIPPROGRAM.URL + '?mpl=' + cast(MEMBERSHIPLEVEL.ID as nvarchar(36)) as [URL],
                            MEMBERSHIPPROGRAM.PRIMARYCONTENTID,
                            [MEMBERSHIPPROGRAM].[ID] as [MEMBERSHIPPROGRAMID],
                            MEMBERSHIPPROGRAM.[ISLIVE] as [MEMBERSHIPPROGAMISLIVE],
                            [MEMBERSHIPLEVEL].[ISACTIVE],
                            [MEMBERSHIPPROGRAM].[NAME] as [MEMBERSHIPPROGRAMNAME],
                            case when ([MEMBERSHIPPROGRAM].PROGRAMTYPECODE = 0 and [MEMBERSHIPPROGRAM].PROGRAMBASEDONCODE = 0) then 1
                            else 0
                            end as ISANNUALDUESBASED,
                            [MEMBERSHIPLEVEL_MICROSITEEMAILTEMPLATE].[ID] as [MEMBERSHIPLEVEL_MICROSITEEMAILTEMPLATEID],
                            coalesce([MEMBERSHIPLEVEL_MICROSITEEMAILTEMPLATE].[ACTIVE], 0) as HASACTIVEACKNOWLEDGEMENTEMAIL
                        from dbo.[MEMBERSHIPLEVEL]
                        inner join MEMBERSHIPPROGRAM_CTE as [MEMBERSHIPPROGRAM]
                            on [MEMBERSHIPLEVEL].[MEMBERSHIPPROGRAMID] = [MEMBERSHIPPROGRAM].[ID]
                        left join dbo.[MICROSITEMEMBERSHIPLEVEL]
                            on [MEMBERSHIPLEVEL].[ID] = [MICROSITEMEMBERSHIPLEVEL].[ID]
                        left outer join dbo.[MEMBERSHIPLEVEL_MICROSITEEMAILTEMPLATE]
                            on [MEMBERSHIPLEVEL].[ID] = [MEMBERSHIPLEVEL_MICROSITEEMAILTEMPLATE].MEMBERSHIPLEVELID
                        cross apply (
                            select case 
                                    when (
                                                [MEMBERSHIPLEVEL].[ISACTIVE] = 1 and 
                                                [MICROSITEMEMBERSHIPLEVEL].[ID] is not null and 
                                                MEMBERSHIPPROGRAM.ISLIVE = 1
                                            ) then 1 
                                    else 0 
                                end 
                            as [ISLIVE]
                        ) as [LIVESTATUS]
                        where 
                            ((coalesce(@LIVEONLY, 0) = 0) or [LIVESTATUS].[ISLIVE] = 1) and
                            (
                                [MEMBERSHIPPROGRAM].[NAMEISMATCH] = 1 or --If the membership program name is a match, we should show all of the levels under it.

                                MEMBERSHIPLEVEL.NAME like '%' + @NAMELIKE + '%'
                            )
                    )
                    select 
                        MEMBERSHIPPROGRAM.ID,
                        MEMBERSHIPPROGRAM.NAME,
                        MEMBERSHIPPROGRAM.DESCRIPTION,
                        MEMBERSHIPPROGRAM.ISBASICCMS,
                        MEMBERSHIPPROGRAM.[ISLIVE],
                        MEMBERSHIPPROGRAM.SITEPAGESID,
                        MEMBERSHIPPROGRAM.URL,
                        MEMBERSHIPPROGRAM.PRIMARYCONTENTID,
                        null as PARENTID,
                        MEMBERSHIPPROGRAM.[ISLIVE] as [MEMBERSHIPPROGAMISLIVE],
                        cast(0 as bit) as ISLEVEL,
                        [ISACTIVE],
                        '50a7cb9a-a8b5-4bd7-95ec-7dac491241d9' as [DETAILVIEWID],
                        MEMBERSHIPPROGRAM.NAME as MEMBERSHIPPROGRAMNAME,
                        MEMBERSHIPPROGRAM.ISANNUALDUESBASED,
                        MEMBERSHIPPROGRAM_MICROSITEEMAILTEMPLATEID as MICROSITEEMAILTEMPLATEID,
                        HASACTIVEACKNOWLEDGEMENTEMAIL
                    from [MEMBERSHIPPROGRAM_CTE] as [MEMBERSHIPPROGRAM]
                    where exists(
                        select 1 
                        from [MEMBERSHIPLEVEL_CTE] as [MEMBERSHIPLEVEL] --Filter programs if there are no program levels meeting the name/live filter rules

                        where [MEMBERSHIPLEVEL].[MEMBERSHIPPROGRAMID] = [MEMBERSHIPPROGRAM].[ID]
                    )

                    union all
                    select
                        ID,
                        NAME,
                        DESCRIPTION,
                        [ISBASICCMS],
                        [ISLIVE],
                        SITEPAGESID,
                        URL,
                        PRIMARYCONTENTID,
                        MEMBERSHIPPROGRAMID as PARENTID,
                        [MEMBERSHIPPROGAMISLIVE],
                        cast(1 as bit) as ISLEVEL,
                        [ISACTIVE],
                        'C102D789-EFA6-4AE4-895F-53A1F110301B' as [DETAILVIEWID],
                        [MEMBERSHIPPROGRAMNAME],
                        ISANNUALDUESBASED,
                        MEMBERSHIPLEVEL_MICROSITEEMAILTEMPLATEID as MICROSITEEMAILTEMPLATEID,
                        HASACTIVEACKNOWLEDGEMENTEMAIL
                    from [MEMBERSHIPLEVEL_CTE]
                    order by MEMBERSHIPPROGRAMNAME, ISLEVEL asc, NAME