USP_MEMBERSHIPLEVEL_PROMOTE

                    CREATE procedure dbo.USP_MEMBERSHIPLEVEL_PROMOTE
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier
                    )
                    as begin
                        set nocount on;

                        if @CHANGEAGENTID is null
                            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                        declare @CURRENTDATE datetime = getdate();
                        declare @CURRENTSEQUENCE int = 0;
                        declare @MEMBERSHIPPROGRAMID uniqueidentifier;
                        declare @MAXSEQUENCE int;

                        declare @PROGRAMTYPECODE tinyint;
                        declare @PROGRAMBASEDONCODE tinyint;
                        declare @LEVELOBTAINCODE tinyint;

                        select
                            @MEMBERSHIPPROGRAMID = MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID,
                            @CURRENTSEQUENCE = MEMBERSHIPLEVEL.SEQUENCE,
                            @LEVELOBTAINCODE = MEMBERSHIPLEVEL.OBTAINLEVELCODE,
                            @PROGRAMTYPECODE = MEMBERSHIPPROGRAM.PROGRAMTYPECODE,
                            @PROGRAMBASEDONCODE = MEMBERSHIPPROGRAM.PROGRAMBASEDONCODE
                        from
                            dbo.MEMBERSHIPLEVEL
                            inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                        where
                            MEMBERSHIPLEVEL.ID = @ID;

                        select @MAXSEQUENCE = max(SEQUENCE)
                        from dbo.MEMBERSHIPLEVEL
                        where MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
                            and OBTAINLEVELCODE = @LEVELOBTAINCODE

                        --If this is the highest level already or the level is contributions based, then throw an exception

                        if @CURRENTSEQUENCE >= @MAXSEQUENCE or (@PROGRAMBASEDONCODE = 1 or (@PROGRAMBASEDONCODE = 2 and @LEVELOBTAINCODE = 1))
                        begin
                            raiserror('BBERR_CANNOTPROMOTE', 13, 1);
                        end

                        declare @LEVELTOSWAPWITHID uniqueidentifier;
                        declare @SEQUENCETOSWAPWITH int;

                        select top 1 @LEVELTOSWAPWITHID = ID,
                                        @SEQUENCETOSWAPWITH = SEQUENCE
                        from dbo.MEMBERSHIPLEVEL
                        where MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
                            and SEQUENCE > @CURRENTSEQUENCE
                            and OBTAINLEVELCODE = @LEVELOBTAINCODE
                            and ISACTIVE = 1
                        order by SEQUENCE asc

                        update dbo.MEMBERSHIPLEVEL
                        set SEQUENCE = @SEQUENCETOSWAPWITH
                        where ID = @ID;

                        update dbo.MEMBERSHIPLEVEL
                        set SEQUENCE = @CURRENTSEQUENCE
                        where ID = @LEVELTOSWAPWITHID;

                    end