UFN_COMPARECONSTITUENTS

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@FIRSTNAME1	nvarchar(50)	IN
@KEYNAME1	nvarchar(100)	IN
@STREETNUMBER1	nvarchar(20)	IN
@STREETNAME1	nvarchar(150)	IN
@POSTCODE1	nvarchar(12)	IN
@FIRSTNAME2	nvarchar(50)	IN
@KEYNAME2	nvarchar(100)	IN
@STREETNUMBER2	nvarchar(20)	IN
@STREETNAME2	nvarchar(150)	IN
@POSTCODE2	nvarchar(12)	IN

Definition

Copy



CREATE function dbo.UFN_COMPARECONSTITUENTS (
  @FIRSTNAME1 nvarchar(50),
  @KEYNAME1 nvarchar(100),
  @STREETNUMBER1 nvarchar(20),
  @STREETNAME1 nvarchar(150),
  @POSTCODE1 nvarchar(12),
  @FIRSTNAME2 nvarchar(50),
  @KEYNAME2 nvarchar(100),
  @STREETNUMBER2 nvarchar(20),
  @STREETNAME2 nvarchar(150),
  @POSTCODE2 nvarchar(12)
)
returns @RESULTS table (
  FIRSTNAMERESULT tinyint,
  KEYNAMERESULT 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,
  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 FIRSTNAMERESULT, KEYNAMERESULT, STREETNUMBERRESULT, STREETNAMERESULT, POSTCODERESULT,
         FIRSTNAMESCORE, KEYNAMESCORE, STREETNUMBERSCORE, STREETNAMESCORE, POSTCODESCORE, HOUSEHOLDMATCH
  from dbo.UFN_COMPARECONSTITUENTS_2(null, @FIRSTNAME1, '', @KEYNAME1, null, @STREETNUMBER1, @STREETNAME1, @POSTCODE1,
                                     null, @FIRSTNAME2, '', @KEYNAME2, null, @STREETNUMBER2, @STREETNAME2, @POSTCODE2, null);

  return
end