USP_MEMBERSHIPLEVEL_DEMOTE

                    CREATE procedure dbo.USP_MEMBERSHIPLEVEL_DEMOTE
                    (
                        @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 @MINSEQUENCE 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 @MINSEQUENCE = min(SEQUENCE)
                        from dbo.MEMBERSHIPLEVEL
                        where MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
                            and OBTAINLEVELCODE = @LEVELOBTAINCODE

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

                        if @CURRENTSEQUENCE <= @MINSEQUENCE or (@PROGRAMBASEDONCODE = 1 or (@PROGRAMBASEDONCODE = 2 and @LEVELOBTAINCODE = 1))
                        begin
                            raiserror('BBERR_CANNOTDEMOTE', 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 desc

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

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

                    end