UFN_FINDCONSTITUENTMATCHES_BYLOOKUPIDS

create function dbo.UFN_FINDCONSTITUENTMATCHES_BYLOOKUPIDS(
  @LOOKUPID nvarchar(100),
  @ALTERNATELOOKUPIDS xml
)
returns @MATCHINGCONSTITUENTS table (
  CONSTITUENTID uniqueidentifier
)
as
begin
    -- match on lookup ID. Found match is exact match. If match is found, we're done

    if @LOOKUPID <> ''
      begin
        insert into @MATCHINGCONSTITUENTS(CONSTITUENTID)
        select ID
        from dbo.CONSTITUENT
        where LOOKUPID = @LOOKUPID;

        if @@ROWCOUNT > 0
          return;

        -- Didn't find a match in the constituent table, try the alternate IDs table.

        -- Constituent update batch's search did this, want to maintain same functionality.

        insert into @MATCHINGCONSTITUENTS(CONSTITUENTID)
        select CONSTITUENTID
        from dbo.ALTERNATELOOKUPID
        where ALTERNATELOOKUPID = @LOOKUPID;

        if @@ROWCOUNT > 0
          return;
      end

    -- match on alternate lookup IDs. Any matches found are exact matches.  If any are found, we're done.

    if @ALTERNATELOOKUPIDS is not null
    begin
      insert into @MATCHINGCONSTITUENTS(CONSTITUENTID)
      select a.CONSTITUENTID
      from (select T.c.value('(ALTERNATELOOKUPID)[1]','nvarchar(max)') AS 'ALTERNATELOOKUPID',
                    T.c.value('(ALTERNATELOOKUPIDTYPECODEID)[1]','uniqueidentifier') AS 'ALTERNATELOOKUPIDTYPECODEID'
            from @ALTERNATELOOKUPIDS.nodes('/ALTERNATELOOKUPIDS/ITEM') T(c)) i
      inner join dbo.ALTERNATELOOKUPID a on a.ALTERNATELOOKUPID = i.ALTERNATELOOKUPID and a.ALTERNATELOOKUPIDTYPECODEID = i.ALTERNATELOOKUPIDTYPECODEID;

    end

  return
end