USP_AUCTIONITEMBATCH_CONSTITUENT_DELETE

            create procedure dbo.USP_AUCTIONITEMBATCH_CONSTITUENT_DELETE
            (
                @BATCHAUCTIONITEMCONSTITUENTID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null output
            )
            as
                set nocount on;

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

                --cache current context information

                declare @contextCache varbinary(128);
                set @contextCache = CONTEXT_INFO();
                --set CONTEXT_INFO to @CHANGEAGENTID

                set CONTEXT_INFO @CHANGEAGENTID;

                -- If the constituent is a group/household or individual that resulted in a household, remove members of the group


                -- First, store which constituents are members

                declare @GROUPMEMBERSANDRELATIONS table
                (
                    BATCHAUCTIONITEMCONSTITUENTID uniqueidentifier
                )

                -- Determine the household ID that may have been generated if the constituent is an individual

                declare @HOUSEHOLDID uniqueidentifier
                select @HOUSEHOLDID = BRCGM.GROUPID
                from dbo.BATCHAUCTIONITEMCONSTITUENTGROUPMEMBER BRCGM
                inner join BATCHAUCTIONITEMCONSTITUENT BRC on BRCGM.GROUPID = BRC.ID
                where
                    BRCGM.MEMBERID = @BATCHAUCTIONITEMCONSTITUENTID and
                    BRC.GROUPTYPECODE = 0 -- Verify group is household


                insert into @GROUPMEMBERSANDRELATIONS (BATCHAUCTIONITEMCONSTITUENTID)
                select MEMBERID from dbo.BATCHAUCTIONITEMCONSTITUENTGROUPMEMBER 
                where 
                    -- Handle when the constituent is a group/household

                    GROUPID = @BATCHAUCTIONITEMCONSTITUENTID or
                    -- Handle household generated by individual

                    GROUPID = @HOUSEHOLDID
                union
                select RELATIONID from dbo.BATCHAUCTIONITEMCONSTITUENTRELATION where CONSTITUENTID = @BATCHAUCTIONITEMCONSTITUENTID

                -- Handle the case when the constituent was a group and their primary contact was an individual or household

                -- (we prevent the user from selecting a group as a primary contact through the form metadata)

                insert into @GROUPMEMBERSANDRELATIONS (BATCHAUCTIONITEMCONSTITUENTID)
                select MEMBERID from dbo.BATCHAUCTIONITEMCONSTITUENTGROUPMEMBER 
                where
                    GROUPID in (select BATCHAUCTIONITEMCONSTITUENTID from @GROUPMEMBERSANDRELATIONS)
                union
                select GROUPID from dbo.BATCHAUCTIONITEMCONSTITUENTGROUPMEMBER 
                where
                    MEMBERID in (select BATCHAUCTIONITEMCONSTITUENTID from @GROUPMEMBERSANDRELATIONS)
                union
                select RELATIONID from dbo.BATCHAUCTIONITEMCONSTITUENTRELATION
                where CONSTITUENTID in (select BATCHAUCTIONITEMCONSTITUENTID from @GROUPMEMBERSANDRELATIONS)

                delete from dbo.BATCHAUCTIONITEMCONSTITUENTGROUPMEMBER 
                where 
                    GROUPID = @BATCHAUCTIONITEMCONSTITUENTID or 
                    GROUPID = @HOUSEHOLDID or
                    GROUPID in (select BATCHAUCTIONITEMCONSTITUENTID from @GROUPMEMBERSANDRELATIONS)

                delete from dbo.BATCHAUCTIONITEMCONSTITUENTRELATION
                where
                    CONSTITUENTID = @BATCHAUCTIONITEMCONSTITUENTID or
                    CONSTITUENTID in (select BATCHAUCTIONITEMCONSTITUENTID from @GROUPMEMBERSANDRELATIONS)

                delete from dbo.BATCHAUCTIONITEMCONSTITUENT 
                where ID in (select BATCHAUCTIONITEMCONSTITUENTID from @GROUPMEMBERSANDRELATIONS)

                if @HOUSEHOLDID is not null
                    delete from dbo.BATCHAUCTIONITEMCONSTITUENT where ID = @HOUSEHOLDID

                --delete the temporary batch version of the constituent

                delete from dbo.BATCHAUCTIONITEMCONSTITUENT where ID = @BATCHAUCTIONITEMCONSTITUENTID

                --reset CONTEXT_INFO to previous value

                if not @contextCache is null
                    set CONTEXT_INFO @contextCache;