UFN_COMPARECONSTITUENTS_2

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@TITLECODE1	uniqueidentifier	IN
@FIRSTNAME1	nvarchar(50)	IN
@MIDDLENAME1	nvarchar(50)	IN
@KEYNAME1	nvarchar(100)	IN
@SUFFIXCODE1	uniqueidentifier	IN
@STREETNUMBER1	nvarchar(20)	IN
@STREETNAME1	nvarchar(150)	IN
@POSTCODE1	nvarchar(12)	IN
@TITLECODE2	uniqueidentifier	IN
@FIRSTNAME2	nvarchar(50)	IN
@MIDDLENAME2	nvarchar(50)	IN
@KEYNAME2	nvarchar(100)	IN
@SUFFIXCODE2	uniqueidentifier	IN
@STREETNUMBER2	nvarchar(20)	IN
@STREETNAME2	nvarchar(150)	IN
@POSTCODE2	nvarchar(12)	IN
@COUNTRYID	uniqueidentifier	IN

Definition

Copy



CREATE function dbo.UFN_COMPARECONSTITUENTS_2 (
  @TITLECODE1 uniqueidentifier,
  @FIRSTNAME1 nvarchar(50),
  @MIDDLENAME1 nvarchar(50),
  @KEYNAME1 nvarchar(100),
  @SUFFIXCODE1 uniqueidentifier,
  @STREETNUMBER1 nvarchar(20),
  @STREETNAME1 nvarchar(150),
  @POSTCODE1 nvarchar(12),
  @TITLECODE2 uniqueidentifier,
  @FIRSTNAME2 nvarchar(50),
  @MIDDLENAME2 nvarchar(50),
  @KEYNAME2 nvarchar(100),
  @SUFFIXCODE2 uniqueidentifier,
  @STREETNUMBER2 nvarchar(20),
  @STREETNAME2 nvarchar(150),
  @POSTCODE2 nvarchar(12),
  @COUNTRYID uniqueidentifier
)
returns @RESULTS table (
  TITLERESULT tinyint,
  FIRSTNAMERESULT tinyint,
  MIDDLENAMERESULT tinyint,
  KEYNAMERESULT tinyint,
  SUFFIXRESULT tinyint,
  STREETNUMBERRESULT tinyint,
  STREETNAMERESULT tinyint,
  POSTCODERESULT 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 @RESULTS
  select TITLERESULT, FIRSTNAMERESULT, MIDDLENAMERESULT, KEYNAMERESULT, SUFFIXRESULT,
         STREETNUMBERRESULT, STREETNAMERESULT, POSTCODERESULT,
         FIRSTNAMESCORE, MIDDLENAMESCORE, KEYNAMESCORE,
         STREETNUMBERSCORE, STREETNAMESCORE, POSTCODESCORE, HOUSEHOLDMATCH
  from dbo.UFN_COMPARECONSTITUENTS_3(@TITLECODE1, @FIRSTNAME1, @MIDDLENAME1, @KEYNAME1, @SUFFIXCODE1, @STREETNUMBER1, @STREETNAME1, @POSTCODE1,
                                     @TITLECODE2, @FIRSTNAME2, @MIDDLENAME2, @KEYNAME2, @SUFFIXCODE2, @STREETNUMBER2, @STREETNAME2, @POSTCODE2,
                                     @COUNTRYID, 0);
  return
end