USP_SIMPLEDATALIST_MEMBERSHIPUPGRADELEVEL

                CREATE procedure dbo.USP_SIMPLEDATALIST_MEMBERSHIPUPGRADELEVEL
                (
                    @MEMBERSHIPID uniqueidentifier
                )
                as

                    set nocount on;

                    select
    ID as VALUE,
    NAME as LABEL
from
(
  --Get all active levels sequenced higher than the current level

    select 
        ML2.ID,
        ML2.NAME,
        ML2.SEQUENCE
    from MEMBERSHIP M
        inner join dbo.MEMBERSHIPLEVEL ML on M.MEMBERSHIPLEVELID = ML.ID
    inner join dbo.MEMBERSHIPLEVEL ML2 on M.MEMBERSHIPPROGRAMID = ML2.MEMBERSHIPPROGRAMID and ML2.ISACTIVE = 1 and ML.SEQUENCE < ML2.SEQUENCE
        inner join dbo.MEMBERSHIPLEVELTERM MLT on ML2.ID  = MLT.LEVELID and MLT.ISACTIVE = 1
    where
        M.ID = @MEMBERSHIPID

    union

  --Get current level if it has active terms that can be upgraded to

    select
        ML.ID,
        ML.NAME,
        ML.SEQUENCE
    from 
        MEMBERSHIP M
        inner join dbo.MEMBERSHIPLEVEL ML on M.MEMBERSHIPLEVELID = ML.ID
        inner join dbo.MEMBERSHIPLEVELTERM MLT on M.MEMBERSHIPLEVELTERMID = MLT.ID and dbo.UFN_MEMBERSHIPLEVELTERM_ISUPGRADEABLE(MLT.ID) = 1
    and ML.ISACTIVE = 1
    where
        M.ID = @MEMBERSHIPID
)UPGRADEABLELEVELS
order by SEQUENCE asc;