USP_GROUP_DISSOLVE

            CREATE procedure dbo.USP_GROUP_DISSOLVE
            (
                @ID uniqueidentifier,
                @DATE datetime = null,
                @CHANGEAGENTID uniqueidentifier = null
            )
            as
            set nocount on;

            declare @CURRENTDATE datetime;

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

            set @CURRENTDATE = getdate();

            begin try

                --Remove all mail preferences (cannot remove members with mail preferences)

                /* cache current context information */
                declare @contextCache varbinary(128);
                set @contextCache = CONTEXT_INFO();

                /* set CONTEXT_INFO to @CHANGEAGENTID */
                if not @CHANGEAGENTID is null
                    set CONTEXT_INFO @CHANGEAGENTID

                delete from
                    dbo.MAILPREFERENCE
                where
                    MAILPREFERENCE.CONSTITUENTID = @ID;

                /* reset CONTEXT_INFO to previous value */
                if not @contextCache is null
                    set CONTEXT_INFO @contextCache                        

                declare @EARLIESTDATE date;
                set @EARLIESTDATE = coalesce(@DATE, @CURRENTDATE);

                --Remove all members

                update
                    dbo.GROUPMEMBERDATERANGE
                set
                    DATETO = @EARLIESTDATE,
                    DATECHANGED = @CURRENTDATE,
                    CHANGEDBYID = @CHANGEAGENTID
                from
                    dbo.GROUPMEMBERDATERANGE
                inner join
                    dbo.GROUPMEMBER
                on
                    GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
                where
                    GROUPMEMBER.GROUPID = @ID
                and
                    (GROUPMEMBERDATERANGE.DATETO is null
                    or
                    GROUPMEMBERDATERANGE.DATETO > @EARLIESTDATE);

                --Remove the primary member flag from the group

                update 
                    dbo.GROUPMEMBER
                set 
                    ISPRIMARY = 0,
                    DATECHANGED = @CURRENTDATE,
                    CHANGEDBYID = @CHANGEAGENTID
                where 
                    GROUPID = @ID
                and
                    ISPRIMARY = 1;

                --Mark group inactive

                update
                    dbo.CONSTITUENT
                set
                    ISINACTIVE = 1,
                    DATECHANGED = @CURRENTDATE,
                    CHANGEDBYID = @CHANGEAGENTID
                where
                    ID = @ID;

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

            return 0;