USP_BBACONSTITUENTFILEIMPORT_DELETE

            create procedure dbo.USP_BBACONSTITUENTFILEIMPORT_DELETE(
                @IMPORTID  nvarchar(100) = '',
                @CHANGEAGENTID uniqueidentifier
            ) as
                set nocount on;

                declare @CURRENTDATE datetime;
                    set @CURRENTDATE = getdate();

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

                begin try                        
                    --get constituent id

                    declare @CONSTITUENTID uniqueidentifier;
                    declare @CONSTITUENTDATEADDED datetime;
                    select
                        @CONSTITUENTID = C.ID,
                        @CONSTITUENTDATEADDED = C.DATEADDED
                    from
                        dbo.CONSTITUENT C
                    left join
                        dbo.TAIMPORTIDMAP MAP on MAP.ID = C.ID
                    where
                        MAP.LOOKUPID = @IMPORTID;

                    if @CONSTITUENTID is not null begin
                        --get spouse id

                        declare @SPOUSEID uniqueidentifier;
                        declare @DELETESPOUSE bit;

                        set @DELETESPOUSE = 0;

                        select
                            @SPOUSEID = S.ID,
                            @DELETESPOUSE = case when S.DATEADDED = @CONSTITUENTDATEADDED and S.DATECHANGED = @CONSTITUENTDATEADDED then 1 else 0 end
                        from
                            dbo.RELATIONSHIP SR
                        left join
                            dbo.CONSTITUENT S on S.ID = SR.RECIPROCALCONSTITUENTID
                        where
                            SR.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and
                            SR.ISSPOUSE = 1;

                        --get constituent household id

                        declare @HOUSEHOLDID uniqueidentifier;
                        select
                            @HOUSEHOLDID = GD.ID
                        from
                            dbo.GROUPMEMBER GM
                        left join
                            dbo.GROUPDATA GD on GD.ID = GM.GROUPID
                        where
                            GM.MEMBERID = @CONSTITUENTID and
                            GD.GROUPTYPECODE = 0;

                        --delete constituent

                        exec dbo.USP_CONSTITUENT_DELETE @CONSTITUENTID, @CHANGEAGENTID;

                        if @SPOUSEID is not null and @DELETESPOUSE = 1 begin
                            --delete spouse

                            exec dbo.USP_CONSTITUENT_DELETE @SPOUSEID, @CHANGEAGENTID;
                        end

                        if @HOUSEHOLDID is not null begin
                            if (select count(ID) from dbo.GROUPMEMBER GM where GM.GROUPID = @HOUSEHOLDID) = 0 begin
                                exec dbo.USP_CONSTITUENT_DELETE @HOUSEHOLDID, @CHANGEAGENTID;
                            end
                        end
                    end
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;