UFN_FINDCONSTITUENTMATCHES_2

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@TITLECODEID	uniqueidentifier	IN
@FIRSTNAME	nvarchar(50)	IN
@MIDDLENAME	nvarchar(50)	IN
@KEYNAME	nvarchar(100)	IN
@SUFFIXCODEID	uniqueidentifier	IN
@ADDRESSBLOCK	nvarchar(150)	IN
@POSTCODE	nvarchar(12)	IN
@COUNTRYID	uniqueidentifier	IN
@ISORGANIZATION	bit	IN
@ISGROUP	bit	IN
@LOOKUPID	nvarchar(100)	IN
@ALTERNATELOOKUPIDS	xml	IN
@EMAILADDRESS	nvarchar(100)	IN
@PHONENUMBER	nvarchar(100)	IN
@AUTOMATCHTHRESHOLD	tinyint	IN

Definition

Copy



CREATE function dbo.UFN_FINDCONSTITUENTMATCHES_2 (
  @TITLECODEID uniqueidentifier,
  @FIRSTNAME nvarchar(50),
  @MIDDLENAME nvarchar(50),
  @KEYNAME nvarchar(100),
  @SUFFIXCODEID uniqueidentifier,
  @ADDRESSBLOCK nvarchar(150),
  @POSTCODE nvarchar(12),
  @COUNTRYID uniqueidentifier,
  @ISORGANIZATION bit,
  @ISGROUP bit,
  @LOOKUPID nvarchar(100),
  @ALTERNATELOOKUPIDS xml,
  @EMAILADDRESS nvarchar(100),
  @PHONENUMBER nvarchar(100),
  @AUTOMATCHTHRESHOLD tinyint
)
returns @MATCHES table (
  CONSTITUENTID uniqueidentifier,
  ADDRESSID uniqueidentifier,
  EMAILADDRESSID uniqueidentifier,
  PHONEID uniqueidentifier,
  --

  IDRESULT tinyint,
  EMAILADDRESSRESULT tinyint,
  TITLERESULT tinyint,
  FIRSTNAMERESULT tinyint,
  MIDDLENAMERESULT tinyint,
  KEYNAMERESULT tinyint,
  SUFFIXRESULT tinyint,
  STREETNUMBERRESULT tinyint,
  STREETNAMERESULT tinyint,
  POSTCODERESULT tinyint,
  PHONENUMBERRESULT tinyint,
  -- scores are included for informational purposes, the results will be what is used to determine matches

  FIRSTNAMESCORE tinyint,
  MIDDLENAMESCORE tinyint,
  KEYNAMESCORE tinyint,
  STREETNUMBERSCORE tinyint,
  STREETNAMESCORE tinyint,
  POSTCODESCORE tinyint,
  -- this constituent is not a match, but the two constituents may be a household

  HOUSEHOLDMATCH bit
)
begin
  insert into @MATCHES
  select 
    CONSTITUENTID,
    ADDRESSID,
    EMAILADDRESSID,
    PHONEID,
    IDRESULT,
    EMAILADDRESSRESULT,
    TITLERESULT,
    FIRSTNAMERESULT,
    MIDDLENAMERESULT,
    KEYNAMERESULT,
    SUFFIXRESULT,
    STREETNUMBERRESULT,
    STREETNAMERESULT,
    POSTCODERESULT,
    PHONENUMBERRESULT,
    FIRSTNAMESCORE,
    MIDDLENAMESCORE,
    KEYNAMESCORE,
    STREETNUMBERSCORE,
    STREETNAMESCORE,
    POSTCODESCORE,
    HOUSEHOLDMATCH
  from dbo.UFN_FINDCONSTITUENTMATCHES_3 (
    @TITLECODEID,
    @FIRSTNAME,
    @MIDDLENAME,
    @KEYNAME,
    @SUFFIXCODEID,
    @ADDRESSBLOCK,
    @POSTCODE,
    @COUNTRYID,
    @ISORGANIZATION,
    @ISGROUP,
    @LOOKUPID,
    @ALTERNATELOOKUPIDS,
    @EMAILADDRESS,
    @PHONENUMBER,
    0, -- @OVERALLMATCHTHRESHOLD, 0: Will return all possible matches with field results, won't bail on scoring

    @AUTOMATCHTHRESHOLD
  )

  return;
end