USP_DATALIST_FAFADDRESSBOOKCONTACTSEARCH

CREATE procedure dbo.USP_DATALIST_FAFADDRESSBOOKCONTACTSEARCH  
(    
 @CLIENTUSERSID int,    
 @EVENTID uniqueidentifier,    
 @SEARCHSTRING varchar(50) = ''    
)    
as    
set nocount on;    

DECLARE @EMPTYGUID uniqueidentifier;    
SET @EMPTYGUID = '00000000-0000-0000-0000-000000000000';    

DECLARE @CONSTITUENTID uniqueidentifier    
SET @CONSTITUENTID = dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERSID)    

DECLARE @PREVIOUSEVENTID uniqueidentifier    
select @PREVIOUSEVENTID = prioryeareventid from dbo.EVENTEXTENSION (NOLOCK) WHERE EVENTID = @EVENTID    

IF ISNULL(@SEARCHSTRING,'') > ''    
SET @SEARCHSTRING = '%'+@SEARCHSTRING+'%'    

 declare @TEAMID uniqueidentifier, @COMPANYID uniqueidentifier    

 DECLARE     
 @IsIndependentHouseholdMember bit     
 ,@IsIndependentHouseholdLeader bit      
 ,@IsIndependentTeamMember bit      
 ,@IsIndependentTeamLeader bit       
 ,@IsCompanyHouseholdMember bit       
 ,@IsCompanyHouseholdLeader bit       
 ,@IsCompanyTeamHouseholdLeader bit       
 ,@IsCompanyTeamMember bit       
 ,@IsCompanyTeamLeader bit       
 ,@IsCompanyLeader bit       
 ,@IsCompanyTeamHouseholdMember bit    
 ,@IsTeamHouseholdMember bit    
 ,@IsTeamHouseholdLeader bit    

 EXEC dbo.USP_FAF_REGISTRANT_ROLE    
 @CONSTITUENTID = @CONSTITUENTID    
 ,@EVENTID =@EVENTID    
 ,@IsIndependentHouseholdMember  = @IsIndependentHouseholdMember OUTPUT    
 ,@IsIndependentHouseholdLeader  = @IsIndependentHouseholdLeader OUTPUT    
 ,@IsIndependentTeamMember  = @IsIndependentTeamMember OUTPUT    
 ,@IsIndependentTeamLeader   = @IsIndependentTeamLeader OUTPUT    
 ,@IsCompanyHouseholdMember   = @IsCompanyHouseholdMember OUTPUT    
 ,@IsCompanyHouseholdLeader   = @IsCompanyHouseholdLeader OUTPUT    
 ,@IsCompanyTeamHouseholdLeader   = @IsCompanyTeamHouseholdLeader OUTPUT    
 ,@IsCompanyTeamMember   = @IsCompanyTeamMember OUTPUT    
 ,@IsCompanyTeamLeader   = @IsCompanyTeamLeader OUTPUT    
 ,@IsCompanyLeader   = @IsCompanyLeader OUTPUT    
 ,@IsCompanyTeamHouseholdMember = @IsCompanyTeamHouseholdMember OUTPUT    
 ,@IsTeamHouseholdMember = @IsTeamHouseholdMember OUTPUT    
 ,@IsTeamHouseholdLeader = @IsTeamHouseholdLeader OUTPUT    

 DECLARE @MYADDRESSBOOK table (    
 GROUPNAME nvarchar(256),    
 CATEGORYSTATUS nvarchar(100),    
 ADDRESSBOOKID uniqueidentifier null,    
 NAME varchar(256) null,    
 CONSTITUENTID uniqueidentifier null,    
 EMAILADDRESS varchar(200) null,    
 REGISTRANTID uniqueidentifier null    
)    



 --show 'My Team'     

 IF (@IsIndependentTeamLeader = 1    
   OR @IsIndependentTeamMember = 1    
   OR @IsCompanyTeamMember = 1    
   OR @IsCompanyTeamLeader = 1    
)      
 BEGIN    
   INSERT INTO @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)    
   SELECT 'Team members', Name, ID    
   FROM dbo.UFN_FAF_TEAMMEMBERLIST(@CONSTITUENTID, @EVENTID ) -- 1=household    


   --don't get the team leaders group here for @IsCompanyTeamLeader, as it has special business logic,

   --and is retrieved later (see --company Team Leaders) section

   IF @IsCompanyTeamLeader = 0
   BEGIN
     INSERT INTO @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)    
     SELECT 'Team leaders', Name, ID    
     FROM dbo.UFN_INDEPENDENTTEAMLEADERLIST(@CONSTITUENTID, @EVENTID) -- 1 = team    

   END

 END    

  --show 'My Team' for company team HH leader & team HH leader    

 if (@IsCompanyTeamHouseholdLeader = 1    
    OR @IsTeamHouseholdLeader = 1    
  )    
  begin    
   SELECT @TEAMID = PARENTTEAMID    
   FROM dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID)   

   INSERT INTO @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)      
   SELECT   
   CASE   
     WHEN TFTC.ID IS NULL THEN 'Team members'  
     WHEN TFTC.ID IS NOT NULL THEN 'Team leaders'  
   END,   
   Name,  
   TML.ID  
   FROM dbo.UFN_FAF_TEAMMEMBERLIST_BY_TEAMID(@TEAMID) TML  
   LEFT OUTER JOIN TEAMFUNDRAISINGTEAMCAPTAIN TFTC ON TML.TEAMID = TFTC.TEAMFUNDRAISINGTEAMID AND TFTC.CONSTITUENTID = TML.ID  
  end    

  --Household leaders    

  IF (@IsCompanyHouseholdLeader = 1    
 OR @IsCompanyHouseholdMember = 1    
 OR @IsCompanyTeamHouseholdLeader = 1    
 OR @IsCompanyTeamHouseholdMember = 1    
 OR @IsTeamHouseholdMember = 1    
 OR @IsTeamHouseholdLeader = 1    
 OR @IsIndependentHouseholdMember = 1    
 OR @IsIndependentHouseholdLeader = 1    
  )    
  BEGIN    
    INSERT INTO  @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)    
    SELECT     
    'Household Leaders',    
    name,    
    ID    
    FROM dbo.UFN_FAF_HOUSEHOLDLEADERLIST(@CONSTITUENTID, @EVENTID)    
  END    

 --Household Members    

 IF (@IsCompanyHouseholdLeader = 1    
 OR @IsCompanyHouseholdMember = 1    
 OR @IsCompanyTeamHouseholdLeader = 1    
 OR @IsCompanyTeamHouseholdMember = 1    
 OR @IsTeamHouseholdMember = 1    
 OR @IsTeamHouseholdLeader = 1    
 OR @IsIndependentHouseholdMember = 1    
 OR @IsIndependentHouseholdLeader = 1    
    )    
 BEGIN    
   INSERT INTO @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)    
   SELECT 'Household members', Name, ID    
   FROM dbo.UFN_FAF_HOUSEHOLDMEMBERLIST(@CONSTITUENTID, @EVENTID)    
    --WHERE ID <> @CONSTITUENTID    

 END    

  --company Team Leaders, need to add "team leaders across"+households under the team + HH leaders across    


  IF(@IsCompanyTeamLeader = 1)    
    BEGIN    
      -- insert team leaders across the company    

       INSERT INTO @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)    
      SELECT     
      'Team Leaders',    
      name,     
      ID    
      FROM dbo.UFN_TEAMLEADERLIST(@CONSTITUENTID, @EVENTID)    
      --where ID <> @CONSTITUENTID    


       select @TEAMID = TEAMID    
       from dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID)    

      if @TEAMID is not null    
      begin    
          --insert households directly under the team     

         insert into @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)    
          select 'Household - ' + TFTC.NAME, FN.Name, FN.id     
          from dbo.TEAMFUNDRAISINGTEAM TFT    
          inner join dbo.TEAMFUNDRAISINGTEAM TFTC    
            on TFT.ID = TFTC.PARENTTEAMID    
          inner join dbo.TEAMEXTENSION TEHH    
            on TFTC.ID = TEHH.TEAMFUNDRAISINGTEAMID     
            and TEHH.TYPECODE =3  --households    

          cross apply dbo.UFN_FAF_HOUSEHOLDMEMBERLIST_BY_HOUSEHOLDID(TFTC.ID) FN    
          where TFT.ID = @TEAMID      

          --also needs Household leaders under my team    

         insert into @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)    
          select 'Household Leaders', C.name, C.ID    
          from dbo.TEAMFUNDRAISINGTEAM TFTHH    
          inner join TEAMEXTENSION TE  -- get those household    

                on TFTHH.ID = TE.TEAMFUNDRAISINGTEAMID      
                and TE.TYPECODE = 3      
          inner join dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC  --retrieve their leaders    

                on TFTHH.ID = TC.TEAMFUNDRAISINGTEAMID    
          inner join dbo.CONSTITUENT C    
                on TC.CONSTITUENTID = C.ID      
          where TFTHH.PARENTTEAMID = @TEAMID               
      end    
    END     

 --for company leaders only, 'My Individuals' and 'My Company', and 'Company Laders'    

 IF (@IsCompanyLeader = 1)    
 BEGIN    
 print @CONSTITUENTID
   INSERT INTO @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)    

   SELECT     
   'Individuals - current', Name, ID    
   FROM dbo.UFN_FAF_COMPANYINDIVIDUALLIST(@CONSTITUENTID, @EVENTID)    

   UNION     

   SELECT -- team leaders, household leaders under this company    

   groupname+' - current', Name, ID    
   FROM dbo.UFN_FAF_COMPANYTEAMLEADERLIST(@CONSTITUENTID, @EVENTID)    

    UNION    

    SELECT 'Company leaders - current', Name, ID    
   FROM dbo.UFN_FAF_COMPANYLEADERLIST(@EVENTID)    

    UNION    

    SELECT 'Company members - current', Name, ID    
    FROM dbo.UFN_FAF_COMPANYMEMBERLIST(@CONSTITUENTID, @EVENTID)    

     --and insert teams and households under the company        

     select @COMPANYID = TEAMID    
     from dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID)    

        print @COMPANYID
     if(@COMPANYID is not null)    
     begin    
        insert into @MYADDRESSBOOK (GROUPNAME, CATEGORYSTATUS, NAME, CONSTITUENTID) 
        select 
            GROUPNAME,
            CASE   
                WHEN TFTC.ID IS NULL THEN 'team members - current'  
                WHEN TFTC.ID IS NOT NULL THEN 'team leaders - current'  
            END as CATEGORYSTATUS,
            NAME, 
            TML.ID from (   
                select 'Team - '+ TFTC.NAME as GROUPNAME, FN.Name, FN.id AS ID, TFTC.ID AS TEAMID     
                from dbo.TEAMFUNDRAISINGTEAM TFT    
                inner join dbo.TEAMFUNDRAISINGTEAM TFTC    
                    on TFT.ID = TFTC.PARENTTEAMID    
                inner join dbo.TEAMEXTENSION TEHH    
                    on TFTC.ID = TEHH.TEAMFUNDRAISINGTEAMID     
                    and TEHH.TYPECODE =1  --team    

                cross apply dbo.UFN_FAF_TEAMMEMBERLIST_BY_TEAMID(TFTC.ID) FN    
                where TFT.ID = @COMPANYID      
            ) TML
            LEFT OUTER JOIN TEAMFUNDRAISINGTEAMCAPTAIN TFTC 
                ON TML.TEAMID = TFTC.TEAMFUNDRAISINGTEAMID 
                AND TFTC.CONSTITUENTID = TML.ID  

        insert into @MYADDRESSBOOK (GROUPNAME, CATEGORYSTATUS, NAME, CONSTITUENTID)    
        SELECT
            GROUPNAME,
            CASE   
                WHEN TFTC.ID IS NULL THEN 'household members - current'  
                WHEN TFTC.ID IS NOT NULL THEN 'household leaders - current'  
            END as CATEGORYSTATUS,
            NAME, 
            TML.ID from (         
                select 'Household - ' + TFTC.NAME AS GROUPNAME, FN.Name, FN.id AS ID, TFTC.ID AS TEAMID      
                from dbo.TEAMFUNDRAISINGTEAM TFT    
                inner join dbo.TEAMFUNDRAISINGTEAM TFTC    
                    on TFT.ID = TFTC.PARENTTEAMID    
                inner join dbo.TEAMEXTENSION TEHH    
                    on TFTC.ID = TEHH.TEAMFUNDRAISINGTEAMID     
                    and TEHH.TYPECODE =3  --households    

                cross apply dbo.UFN_FAF_HOUSEHOLDMEMBERLIST_BY_HOUSEHOLDID(TFTC.ID) FN    
                where TFT.ID = @COMPANYID      
            ) TML
            LEFT OUTER JOIN TEAMFUNDRAISINGTEAMCAPTAIN TFTC 
                ON TML.TEAMID = TFTC.TEAMFUNDRAISINGTEAMID 
                AND TFTC.CONSTITUENTID = TML.ID  
     end    
  END    

 -- previous members    


  DECLARE @MYADDRESSBOOKPREVIOUS table (    
       GROUPNAME nvarchar(100),    
       NAME varchar(256) null,    
       CONSTITUENTID uniqueidentifier null    
       )    

    IF @PREVIOUSEVENTID IS NOT NULL    
    Begin    

      IF (@IsIndependentTeamLeader = 1    
       OR @IsIndependentTeamMember = 1    
       OR @IsCompanyTeamMember = 1    
       OR @IsCompanyTeamLeader = 1)      
     BEGIN    
       INSERT INTO @MYADDRESSBOOKPREVIOUS (GROUPNAME, NAME, CONSTITUENTID)    
       SELECT 'Team members - Previous', Name, ID    
       FROM dbo.UFN_FAF_TEAMMEMBERLIST(@CONSTITUENTID, @PREVIOUSEVENTID) -- 1 = team    

        where ID not in (Select constituentid from dbo.REGISTRANT (NOLOCK) where EVENTID = @EVENTID)    
     END    

      IF (@IsCompanyHouseholdLeader = 1    
  OR @IsCompanyHouseholdMember = 1    
  OR @IsCompanyTeamHouseholdLeader = 1    
  OR @IsCompanyTeamHouseholdMember = 1    
  OR @IsTeamHouseholdMember = 1    
  OR @IsTeamHouseholdLeader = 1    
  OR @IsIndependentHouseholdMember = 1    
  OR @IsIndependentHouseholdLeader = 1    
        )    
     BEGIN    
       INSERT INTO @MYADDRESSBOOKPREVIOUS (GROUPNAME, NAME, CONSTITUENTID)    
       SELECT 'Household members - Previous', Name, ID    
       FROM dbo.UFN_FAF_HOUSEHOLDMEMBERLIST(@CONSTITUENTID, @PREVIOUSEVENTID)    
        WHERE ID not in (Select constituentid from dbo.REGISTRANT (NOLOCK) where EVENTID = @EVENTID)    
     END    

      IF (@IsCompanyLeader = 1)    
     BEGIN    
       INSERT INTO @MYADDRESSBOOKPREVIOUS (GROUPNAME, NAME, CONSTITUENTID)    

       SELECT     
       'Individuals - Previous', Name, ID    
       FROM dbo.UFN_FAF_COMPANYINDIVIDUALLIST(@CONSTITUENTID, @PREVIOUSEVENTID)    
        WHERE ID not in (Select constituentid from dbo.REGISTRANT (NOLOCK) where EVENTID = @EVENTID)    

       UNION     

       SELECT -- team leaders, household leaders under this company    

       groupname+' - Previous', Name, ID    
       FROM dbo.UFN_FAF_COMPANYTEAMLEADERLIST(@CONSTITUENTID, @PREVIOUSEVENTID)    
        WHERE ID not in (Select constituentid from dbo.REGISTRANT (NOLOCK) where EVENTID = @EVENTID)    

     END    

   End    -- previous members    



  INSERT INTO @MYADDRESSBOOK (GROUPNAME, NAME, CONSTITUENTID)    
  SELECT PA.GROUPNAME,PA.NAME,PA.CONSTITUENTID    
  FROM @MYADDRESSBOOKPREVIOUS PA    
  LEFT OUTER JOIN @MYADDRESSBOOK MA    
  ON MA.CONSTITUENTID = PA.CONSTITUENTID    
  WHERE MA.CONSTITUENTID IS NULL    
/*      
     INSERT INTO @MYADDRESSBOOK (GROUPNAME, ADDRESSBOOKID, NAME, CONSTITUENTID, EMAILADDRESS)     
        SELECT    
        (CASE ISNULL(D.STATUS,'S')   
     WHEN 'A' THEN 'Donors - current'      
    WHEN 'P' THEN 'Donors - previous'    
    END), D.CONTACTID, ltrim(rtrim(replace(isnull(A.FIRSTNAME+' ', '') + isnull(A.LASTNAME, ''), '  ', ' '))), D.CONSTITUENTID, A.EMAILADDRESS  
    FROM dbo.UFN_FAFADDRESSBOOK_DONORS_LIST(@EVENTID, @CLIENTUSERSID) D  
    INNER JOIN dbo.ADDRESSBOOKFAF A (NOLOCK) ON A.ID = D.CONTACTID      
*/

  INSERT INTO @MYADDRESSBOOK (GROUPNAME, ADDRESSBOOKID, NAME, CONSTITUENTID, EMAILADDRESS)     
  select 'Donors - current', ABF.ID, ltrim(rtrim(replace(isnull(ABF.FIRSTNAME+' ', '') + isnull(ABF.LASTNAME, ''), '  ', ' '))), ABF.CONSTITUENTID, ABF.EMAILADDRESS 
  from REVENUERECOGNITION RR
  inner join FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = RR.REVENUESPLITID 
  INNER JOIN REVENUESPLIT_EXT RS_EXT on FTLI.ID = RS_EXT.ID 
  inner join FINANCIALTRANSACTION FT on FT.ID = FTLI.FINANCIALTRANSACTIONID 
  inner join REVENUE_EXT R_EXT on R_EXT.ID = FT.ID 
  inner join EVENT E on R_EXT.APPEALID = E.APPEALID 
  inner join ADDRESSBOOKFAF ABF on ABF.CONSTITUENTID = FT.CONSTITUENTID and ABF.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@CONSTITUENTID))
  where 
      RR.CONSTITUENTID in 
    (
          select @CONSTITUENTID
                  union
          select TE.TEAMCONSTITUENTID from TEAMFUNDRAISINGTEAMCAPTAIN  TFTC
          inner join TEAMFUNDRAISINGTEAM TFT on TFTC.TEAMFUNDRAISINGTEAMID = TFT.ID 
          inner join TEAMEXTENSION TE on TFT.ID = TE.TEAMFUNDRAISINGTEAMID 
          where TE.EVENTID = @EVENTID 
          and TFTC.CONSTITUENTID = @CONSTITUENTID 
      )
    and E.ID = @EVENTID
      and not (FT.TYPECODE = 0 and RS_EXT.APPLICATIONCODE = 1)

  if @PREVIOUSEVENTID is not null
  begin
    INSERT INTO @MYADDRESSBOOK (GROUPNAME, ADDRESSBOOKID, NAME, CONSTITUENTID, EMAILADDRESS)     
    select 'Donors - previous', ABF.ID, ltrim(rtrim(replace(isnull(ABF.FIRSTNAME+' ', '') + isnull(ABF.LASTNAME, ''), '  ', ' '))), ABF.CONSTITUENTID, ABF.EMAILADDRESS 
    from REVENUERECOGNITION RR
    inner join FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = RR.REVENUESPLITID 
    INNER JOIN REVENUESPLIT_EXT RS_EXT on FTLI.ID = RS_EXT.ID 
    inner join FINANCIALTRANSACTION FT on FT.ID = FTLI.FINANCIALTRANSACTIONID 
    inner join REVENUE_EXT R_EXT on R_EXT.ID = FT.ID 
    inner join EVENT E on R_EXT.APPEALID = E.APPEALID 
    inner join ADDRESSBOOKFAF ABF on ABF.CONSTITUENTID = FT.CONSTITUENTID and ABF.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@CONSTITUENTID))  
    where 
        RR.CONSTITUENTID in
      (
            select @CONSTITUENTID
                    union
            select TE.TEAMCONSTITUENTID from TEAMFUNDRAISINGTEAMCAPTAIN  TFTC
            inner join TEAMFUNDRAISINGTEAM TFT on TFTC.TEAMFUNDRAISINGTEAMID = TFT.ID 
            inner join TEAMEXTENSION TE on TFT.ID = TE.TEAMFUNDRAISINGTEAMID 
            where TE.EVENTID = @PREVIOUSEVENTID 
            and TFTC.CONSTITUENTID = @CONSTITUENTID 
        )
        and E.ID = @PREVIOUSEVENTID
        and not (FT.TYPECODE = 0 and RS_EXT.APPLICATIONCODE = 1)
  end

  INSERT INTO @MYADDRESSBOOK (GROUPNAME, ADDRESSBOOKID, NAME, CONSTITUENTID, EMAILADDRESS)    
  select 'Contacts', ID, ltrim(rtrim(replace(isnull(FIRSTNAME+' ', '') + isnull(MIDDLENAME+' ', '') + isnull(LASTNAME, ''), '  ', ' '))) as KEYNAME,    
  CONSTITUENTID, EMAILADDRESS    
  FROM dbo.ADDRESSBOOKFAF WITH (NOLOCK)    
  where CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@CONSTITUENTID))    
  AND ISNULL(CONSTITUENTID,@EMPTYGUID) NOT IN (SELECT CONSTITUENTID FROM @MYADDRESSBOOK)    
  AND ISNULL(CONSTITUENTID,@EMPTYGUID) NOT IN (SELECT CONSTITUENTID FROM dbo.REGISTRANT (NOLOCK) WHERE EVENTID = @EVENTID)    

  UPDATE MA    
  SET EMAILADDRESS = E.EMAILADDRESS    
  FROM @MYADDRESSBOOK MA    
  INNER JOIN dbo.EMAILADDRESS E WITH (NOLOCK)    
  ON MA.CONSTITUENTID = E.CONSTITUENTID    
  AND E.ISPRIMARY = 1    
  WHERE MA.GROUPNAME <> 'Contacts'    

  UPDATE MA    
 SET EMAILADDRESS = (CASE WHEN ISNULL(AB.EMAILADDRESS,'') > '' THEN AB.EMAILADDRESS ELSE MA.EMAILADDRESS END),    
    ADDRESSBOOKID = AB.ID    
 FROM @MYADDRESSBOOK MA    
 INNER JOIN dbo.ADDRESSBOOKFAF AB WITH (NOLOCK)    
 ON MA.CONSTITUENTID = AB.CONSTITUENTID    
  where AB.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@CONSTITUENTID))      

  UPDATE MA    
  SET REGISTRANTID = R.ID    
  FROM @MYADDRESSBOOK MA    
  INNER JOIN dbo.REGISTRANT R WITH (NOLOCK)    
  ON R.CONSTITUENTID = MA.CONSTITUENTID    
  AND R.EVENTID = @EVENTID    
  WHERE MA.CONSTITUENTID IS NOT NULL    

  UPDATE MA      
  SET ADDRESSBOOKID = NULL      
  FROM @MYADDRESSBOOK MA     
  WHERE ISNULL(MA.CONSTITUENTID, @EMPTYGUID) = @CONSTITUENTID  


 IF ISNULL(@SEARCHSTRING,'') > ''      
 BEGIN      
 SELECT      
  GROUPNAME,      
  CASE   
    WHEN CATEGORYSTATUS IS NULL AND ADDRESSBOOKID IS NOT NULL THEN LOWER(GROUPNAME)  
    WHEN CATEGORYSTATUS IS NOT NULL AND ADDRESSBOOKID IS NOT NULL THEN LOWER(CATEGORYSTATUS)  
  END AS CATEGORYSTATUS,      
  ADDRESSBOOKID,      
  NAME,      
  CONSTITUENTID,      
  EMAILADDRESS,      
  REGISTRANTID      
 FROM @MYADDRESSBOOK       
 WHERE NAME LIKE @SEARCHSTRING      
 UNION      
 SELECT      
  GROUPNAME,      
  CASE   
    WHEN CATEGORYSTATUS IS NULL AND ADDRESSBOOKID IS NOT NULL THEN LOWER(GROUPNAME)  
    WHEN CATEGORYSTATUS IS NOT NULL AND ADDRESSBOOKID IS NOT NULL THEN LOWER(CATEGORYSTATUS)  
  END AS CATEGORYSTATUS,      
  ADDRESSBOOKID,      
  NAME,      
  CONSTITUENTID,      
  EMAILADDRESS,      
  REGISTRANTID      
 FROM @MYADDRESSBOOK       
 WHERE GROUPNAME LIKE @SEARCHSTRING     
 END      
 ELSE      
 BEGIN      
 SELECT      
  GROUPNAME,      
  CASE   
    WHEN CATEGORYSTATUS IS NULL AND ADDRESSBOOKID IS NOT NULL THEN LOWER(GROUPNAME) 
    WHEN CATEGORYSTATUS IS NOT NULL AND ADDRESSBOOKID IS NOT NULL THEN LOWER(CATEGORYSTATUS)  
  END AS CATEGORYSTATUS,      
  ADDRESSBOOKID,      
  NAME,      
  CONSTITUENTID,      
  EMAILADDRESS,      
  REGISTRANTID      
 FROM @MYADDRESSBOOK       
 END