USP_SIMPLEDATALIST_SPONSORSHIPREPORTDIMENSIONS

CREATE procedure dbo.USP_SIMPLEDATALIST_SPONSORSHIPREPORTDIMENSIONS(
 @DIMENSION tinyint = 0,
 @EXCLUDEAFFILIATEPROGRAMS bit = 0
)as
   -- Program

    select 
        ID as VALUE, 
        NAME as LABEL,
        NAME DESCRIPTION
    from dbo.SPONSORSHIPPROGRAM SP
    where (@DIMENSION = 0 or (@DIMENSION=22 and ISINACTIVE=0))
    and (@EXCLUDEAFFILIATEPROGRAMS = 0 
        or (@EXCLUDEAFFILIATEPROGRAMS = 1 and not exists(select 'x' from dbo.SPONSORSHIPAFFILIATEPROGRAM SA where SA.ID = SP.ID)))
    union all
    -- Channel

    select 
        ID as VALUE, 
        DESCRIPTION as LABEL,
        DESCRIPTION DESCRIPTION
    from dbo.CHANNELCODE
    where @DIMENSION in (1, 12)
    union all
    -- Location Types

    select 
        ID as VALUE, 
        DESCRIPTION as LABEL,
        cast(SEQUENCE as CHAR) DESCRIPTION
    from dbo.SPONSORSHIPLOCATIONTYPECODE
    where @DIMENSION = 2
    union all
    -- Appeal

    select 
        ID as VALUE, 
        NAME as LABEL,
        NAME DESCRIPTION
    from dbo.APPEAL
    where @DIMENSION in (3, 13)
  union all
  -- Reason

    select 
        ID as VALUE, 
        REASON as LABEL,
        REASON DESCRIPTION
    from dbo.SPONSORSHIPREASON
    where @DIMENSION = 6 and REASONTYPECODE in (6,7)
  union all
  -- Tenure

  select 
    ID as VALUE, 
    DESCRIPTION as LABEL, 
    DESCRIPTION DESCRIPTION
  from dbo.SPONSORSHIPCANCELLATIONREPORTTENURECODE
  where @DIMENSION=7
    union all
        -- Age Range

    select
        ID as VALUE,
        DISPLAYNAME as LABEL,
        RIGHT('00'+convert(nvarchar,MINAGE),2) DESCRIPTION
    from dbo.SPONSORSHIPOPPORTUNITYAGERANGE
    where @DIMENSION = 8
  union all
    -- Operations report reasons

    select 
        ID as VALUE, 
        REASON as LABEL,
        REASON DESCRIPTION
    from dbo.SPONSORSHIPREASON
    where @DIMENSION = 15 and REASONTYPECODE in (1,2,4,5,6,7,9)
  union all
   -- Affiliate Program

    select 
        ID as VALUE, 
        NAME as LABEL,
        NAME DESCRIPTION
    from dbo.SPONSORSHIPPROGRAM
    where @DIMENSION = 16
  and ID in (select ID from dbo.SPONSORSHIPAFFILIATEPROGRAM)
  union all
   -- Non-affiliate Program

    select 
        ID as VALUE, 
        NAME as LABEL,
        NAME DESCRIPTION
    from dbo.SPONSORSHIPPROGRAM
    where @DIMENSION = 18
  and ID not in (select ID from dbo.SPONSORSHIPAFFILIATEPROGRAM)
    union all
    -- Group

    select 
      ID as VALUE,
        NAME as LABEL,
        NAME as DESCRIPTION
    from dbo.SPONSORSHIPOPPORTUNITYGROUP
    where @DIMENSION = 19
    union all
    -- Project Category

    select
       ID as VALUE,
         DESCRIPTION as LABEL,
         DESCRIPTION as DESCRIPTION
    from dbo.SPROPPPROJECTCATEGORYCODE
    where @DIMENSION = 20
  union all
  -- Number of sponsorships

    select
       '15220822-AE9C-472a-A16A-1523AB107030' as VALUE,
         '1 sponsorship' as LABEL,
         '1 sponsorship' as DESCRIPTION
    where @DIMENSION = 21
  union all
  select
       'DB2A0940-01D0-4bdf-BABD-E35D7397CD60' as VALUE,
         '2 sponsorships' as LABEL,
         '2 sponsorships' as DESCRIPTION
    where @DIMENSION = 21
  union all
  select
       '29C7D09B-87BB-4a6c-9499-330F0405BFEE' as VALUE,
         '3 sponsorships' as LABEL,
         '3 sponsorships' as DESCRIPTION
    where @DIMENSION = 21
  union all
  select
       '261295FA-C69B-4669-8A22-B489E445C485' as VALUE,
         '4+ sponsorships' as LABEL,
         '4+ sponsorships' as DESCRIPTION
    where @DIMENSION = 21
  union all
  select 
    ID as VALUE,
    NAME as LABEL,
    NAME as DESCRIPTION
  from dbo.MKTSEGMENT where SEGMENTTYPECODE= 5 and ISSYSTEM = 0 and @DIMENSION=26
  union all
  select 
    ID as VALUE,
    NAME as LABEL,
    DESCRIPTION as DESCRIPTION
  from dbo.MKTPACKAGE where ISSYSTEM = 0 and @DIMENSION=27
  order by DESCRIPTION