USP_INSERTSEARCHCONSTITUENT

CREATE procedure dbo.USP_INSERTSEARCHCONSTITUENT (
  @TABLENAME as nvarchar(128) = null,  -- Null or empty string will repopulate the entire SEARCHCONSTITUENT table.

  @CONSTITUENTCOLUMN as nvarchar(128) = 'CONSTITID',
  @ROWSCREATED int = null output
) as
begin
  declare @SQL nvarchar(max)

  set @SQL =
  'insert into dbo.SEARCHCONSTITUENT (ID,CONSTITUENTID,ADDRESSID,
                                     KEYNAME,FIRSTNAME,MIDDLENAME,NAMETYPECODE,
                                     TITLECODEID,SUFFIXCODEID,
                                     COUNTRYID,POSTCODE,STREETNUMBER,STREETNAME,
                                     ISORGANIZATION,ISGROUP,ALIASID)
  select newid(),
         c.ID,
         a.ID,
         c.KEYNAME,
         c.FIRSTNAME,
         c.MIDDLENAME,
         c.NAMETYPECODE,
         c.TITLECODEID,
         c.SUFFIXCODEID,
         a.COUNTRYID,
         case when ltrim(rtrim(a.POSTCODE)) like ''[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'' then left(ltrim(a.POSTCODE),5) else isnull(ltrim(rtrim(a.POSTCODE)),'''') end,
         isnull(PARSEDADDRESS.STREETNUMBER,''''),
         isnull(isnull(PARSEDADDRESS.STREETNAME,ab.CLEANADDRESSBLOCK),''''),
         c.ISORGANIZATION,
         c.ISGROUP,
         c.ALIASID
  from ' +
  case when isnull(@TABLENAME,'') <> '' then @TABLENAME + ' s inner join dbo.V_CONSTITUENTALLNAMES c on c.ID = s.' + @CONSTITUENTCOLUMN
       else 'dbo.V_CONSTITUENTALLNAMES c'
  end +
' left outer join ADDRESS a on a.CONSTITUENTID = c.ID
  outer apply (select ltrim(rtrim(upper(replace(replace(replace(replace(replace(replace(replace(replace(
                       a.ADDRESSBLOCK,''.'',''''),''-'','' ''),'','','' ''),
                       char(13)+char(10),'' ''),char(10)+char(13),'' ''),char(13),'' ''),char(10),'' ''),''  '','' '')))) CLEANADDRESSBLOCK) ab
  outer apply (select left(CLEANADDRESSBLOCK,charindex('' '',CLEANADDRESSBLOCK)-1) STREETNUMBER,
                      right(CLEANADDRESSBLOCK,len(CLEANADDRESSBLOCK)-charindex('' '',CLEANADDRESSBLOCK)) STREETNAME
               where dbo.UFN_REGULAREXPRESSIONMATCH(''(?:^[A-Z]?[\d-]+[A-Z]? (?:.|\n)+$)'',CLEANADDRESSBLOCK) = 1
               and charindex('' '',CLEANADDRESSBLOCK)<=13) PARSEDADDRESS
  where c.KEYNAME<>''''';

  exec sp_executesql @SQL

  set @ROWSCREATED = @@ROWCOUNT
end