USP_MEMBERSHIPLEVEL_DELETE

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

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

                        declare @CURRENTDATE datetime = getdate();
                        declare @MEMBERSHIPPROGRAMID uniqueidentifier;

                        select @MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAMID from dbo.MEMBERSHIPLEVEL where ID = @ID;

                        begin try

                            declare @REMAININGLEVELCOUNT int 

                            select @REMAININGLEVELCOUNT = count(*)
                                from MEMBERSHIPLEVEL
                                    join (
                                        select MEMBERSHIPPROGRAM.ID
                                        from MEMBERSHIPLEVEL
                                            join MEMBERSHIPPROGRAM on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                                        where MEMBERSHIPLEVEL.ID = @ID
                                    ) a on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = a.ID
                                where MEMBERSHIPLEVEL.ID <> @ID
                                and MEMBERSHIPLEVEL.ISACTIVE = 1

                            if (@REMAININGLEVELCOUNT = 0)
                            begin
                                raiserror('BBERR_CANNOTDELETELASTLEVEL',13,1)
                            end

                            exec USP_MEMBERSHIPLEVEL_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;

                            -- Use identity column to try and resequence the membership levels in case invalid or duplicate sequences were inserted

                            declare @TEMPMEMBERSHIPLEVELS table (
                                ID uniqueidentifier,
                                SEQUENCE int identity(0,1)
                            );

                            insert into @TEMPMEMBERSHIPLEVELS
                                select ID
                                from dbo.MEMBERSHIPLEVEL
                                where MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
                                order by SEQUENCE;

                            update
                                dbo.MEMBERSHIPLEVEL
                            set
                                MEMBERSHIPLEVEL.SEQUENCE = TEMPMEMBERSHIPLEVELS.SEQUENCE,
                                MEMBERSHIPLEVEL.CHANGEDBYID = @CHANGEAGENTID,
                                MEMBERSHIPLEVEL.DATECHANGED = @CURRENTDATE
                            from
                                dbo.MEMBERSHIPLEVEL
                            inner join
                                @TEMPMEMBERSHIPLEVELS as TEMPMEMBERSHIPLEVELS on TEMPMEMBERSHIPLEVELS.ID = MEMBERSHIPLEVEL.ID
                            where
                                MEMBERSHIPLEVEL.SEQUENCE <> TEMPMEMBERSHIPLEVELS.SEQUENCE;
                        end try

                        begin catch
                            exec dbo.USP_RAISE_ERROR;
                            return 1;
                        end catch

                        return 0;