USP_DATALIST_MEMBERSHIPLEVELCAMPAIGN

Returns a list of campaigns associated with a membership level.
Parameters

Parameter	Parameter Type	Mode	Description
@MEMBERSHIPLEVELID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
Definition

Copy


CREATE procedure dbo.USP_DATALIST_MEMBERSHIPLEVELCAMPAIGN(
@MEMBERSHIPLEVELID uniqueidentifier
)
as
    set nocount on;

  declare @override_on bit = 0;
    set @override_on = (select OVERRIDECAMPAIGNS from dbo.MEMBERSHIPLEVEL where ID = @MEMBERSHIPLEVELID);

    if @override_on = 1
            begin
                    select
                            C.ID,
                            C.USERID,
                            C.NAME,
                            CAMPAIGNPRIORITYTYPECODE.DESCRIPTION + ' - ' + CAMPAIGNSUBPRIORITYNAMECODE.DESCRIPTION as [SUBPRIORITY],
                            MLC.DATEFROM,
                            MLC.DATETO
                        from 
                            dbo.MEMBERSHIPLEVELCAMPAIGN MLC
                        inner join 
                            dbo.CAMPAIGN C on MLC.CAMPAIGNID = C.ID
                        left outer join 
                            dbo.CAMPAIGNSUBPRIORITY on CAMPAIGNSUBPRIORITY.ID = CAMPAIGNSUBPRIORITYID
                        left outer join
                            dbo.CAMPAIGNPRIORITY on CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID = CAMPAIGNPRIORITY.ID
                        left outer join
                            dbo.CAMPAIGNPRIORITYTYPECODE on CAMPAIGNPRIORITY.CAMPAIGNPRIORITYTYPECODEID = CAMPAIGNPRIORITYTYPECODE.ID
                        left join 
                            dbo.CAMPAIGNSUBPRIORITYNAMECODE on CAMPAIGNSUBPRIORITYNAMECODE.ID = CAMPAIGNSUBPRIORITY.CAMPAIGNSUBPRIORITYNAMECODEID
                        where 
                            MLC.MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID
            end
    else
        begin
                    select
                            C.ID,
                            C.USERID,
                            C.NAME,
                            CAMPAIGNPRIORITYTYPECODE.DESCRIPTION + ' - ' + CAMPAIGNSUBPRIORITYNAMECODE.DESCRIPTION as [SUBPRIORITY],
                            MPC.DATEFROM,
                            MPC.DATETO
                        from 
                          dbo.MEMBERSHIPLEVEL ML
                        inner join
                            dbo.MEMBERSHIPPROGRAMCAMPAIGN MPC on ML.MEMBERSHIPPROGRAMID = MPC.MEMBERSHIPPROGRAMID
                        inner join 
                            dbo.CAMPAIGN C on MPC.CAMPAIGNID = C.ID
                        left outer join 
                            dbo.CAMPAIGNSUBPRIORITY on CAMPAIGNSUBPRIORITY.ID = CAMPAIGNSUBPRIORITYID
                        left outer join
                            dbo.CAMPAIGNPRIORITY on CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID = CAMPAIGNPRIORITY.ID
                        left outer join
                            dbo.CAMPAIGNPRIORITYTYPECODE on CAMPAIGNPRIORITY.CAMPAIGNPRIORITYTYPECODEID = CAMPAIGNPRIORITYTYPECODE.ID
                        left join 
                            dbo.CAMPAIGNSUBPRIORITYNAMECODE on CAMPAIGNSUBPRIORITYNAMECODE.ID = CAMPAIGNSUBPRIORITY.CAMPAIGNSUBPRIORITYNAMECODEID
                        where 
                            ML.ID = @MEMBERSHIPLEVELID            
            end