As requested here is the complete sproc:

CREATE PROCEDURE [dbo].[usp_GenerateKeys]

@SourceTable VARCHAR(50)

AS

BEGIN

SET NOCOUNT ON

DECLARE @SQL NVARCHAR(MAX)

DECLARE @MSG NVARCHAR(500)

-- Get source table unique reference

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'FieldMappings')

DROP TABLE dbo.FieldMappings

select Label, FieldOrder into dbo.FieldMappings

from [Snapshot_MeritDirect].dbo.BFieldTypes

WHERE Label in (select column_name from Snapshot_MeritDirect_Load.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @SourceTable)

-- We need a table to hold all the fields and their definsitions, then based on what columns

-- the staging file has, we build the keys table

DECLARE @SourceTableUniqueReference VARCHAR(50)

-- Used to generate the insert into the keys table

DECLARE @InsertIntoPart VARCHAR(4000) = ''

DECLARE @SelectPart VARCHAR(4000) = ''

DECLARE @FromPart VARCHAR(4000) = ''

DECLARE @CROSSAPPLY VARCHAR(200) = ''

-- Person name details

DECLARE @mkNameKeySELECTString VARCHAR(800) = 'NULL'

DECLARE @mkName1SELECTString VARCHAR(800) = 'NULL'

DECLARE @mkName2SELECTString VARCHAR(800) = 'NULL'

DECLARE @mkName3SELECTString VARCHAR(800) = 'NULL'

DECLARE @mkNormalisedName VARCHAR(800) = 'NULL'

-- Company specific keys

DECLARE @mkOrgNameKeyString VARCHAR(800) = 'NULL'

DECLARE @mkOrgNameSELECTString VARCHAR(800) = 'NULL'

DECLARE @mkOrgName1SELECTString VARCHAR(800) = 'NULL'

DECLARE @mkOrgName2SELECTString VARCHAR(800) = 'NULL'

DECLARE @mkOrgName3SELECTString VARCHAR(800) = 'NULL'

DECLARE @mkNormalisedOrganisation VARCHAR(800) = 'NULL'

-- Address specific details

DECLARE @mkPostInSELECTString NVARCHAR(800) = 'NULL'

DECLARE @mkPostOutSELECTString NVARCHAR(800) = 'NULL'

DECLARE @mkPhoneticStreetSELECTString NVARCHAR(800) = 'NULL'

DECLARE @mkPremiseSELECTString NVARCHAR(800) = 'NULL'

DECLARE @mkTownSELECTString NVARCHAR(800) = 'NULL'

SET @SourceTableUniqueReference = 'ID, GUID '

---- If we already have First and Last names then there is no need to split the names

IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Forename')

AND EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Surname')

BEGIN

-- Phonetic Surname + First letter of Forename

SET @mkNameKeySELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetLastWord(Surname) + LEFT(dbo.GetFirstWord(Forename), 1))'

-- Phonetic Surname

SET @mkName1SELECTString = 'dbo.clrFn_DoubleMetaphone(Surname)'

-- Phonetic Forename

SET @mkName2SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetFirstWord(Forename))'

-- Phonetic Middle name or Initial

SET @mkName3SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetSecondWord(Forename))'

-- Normalised name = Surname + Forename + Middle Name

SET @mkNormalisedName = 'dbo.GetLastWord(Surname) + '','' + dbo.GetFirstWord(Forename) + '','' + dbo.GetSecondWord(Forename)'

END

else IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Forename')

AND NOT EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Surname')

BEGIN

-- Phonetic Surname + First letter of Forename

SET @mkNameKeySELECTString = 'NULL'

-- Phonetic Surname

SET @mkName1SELECTString = 'NULL'

-- Phonetic Forename

SET @mkName2SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetFirstWord(Forename))'

-- Phonetic Middle name or Initial

SET @mkName3SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetSecondWord(Forename))'

-- Normalised name = Forename + Middle Name

SET @mkNormalisedName = 'dbo.GetFirstWord(Forename) + '','' + dbo.GetSecondWord(Forename)'

END

else IF NOT EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Forename')

AND EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Surname')

BEGIN

-- Phonetic Surname + First letter of Forename

SET @mkNameKeySELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetLastWord(Surname))'

-- Phonetic Surname

SET @mkName1SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetLastWord(Surname))'

-- Phonetic Forename

SET @mkName2SELECTString = 'NULL'

-- Phonetic Middle name or Initial

SET @mkName3SELECTString = 'NULL'

-- Normalised name = Surname

SET @mkNormalisedName = 'dbo.GetLastWord(Surname) '

END

ELSE IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'FullContactName')

BEGIN

-- Phonetic Surname + First letter of Forename

SET @mkNameKeySELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetLastWord(FullContactName) + LEFT(dbo.GetFirstWord(FullContactName), 1))'

-- Phonetic Surname

SET @mkName1SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetLastWord(FullContactName))'

-- Phonetic Forename

SET @mkName2SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetFirstWord(FullContactName))'

-- Phonetic Middle name or Initial

SET @mkName3SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetSecondWord(FullContactName))'

-- Normalised name = Surname + Forename + Middle Name

SET @mkNormalisedName = 'dbo.GetLastWord(FullContactName) + '','' + dbo.GetFirstWord(FullContactName) + '','' + dbo.GetSecondWord(FullContactName)'

END

ELSE

BEGIN

SET @MSG = N'No Forename, Surname or Full Contact Details present in the table.'

RAISERROR(@MSG,16,1);

RETURN

END

-- If we have compnay details then we need to break this down into 4 different keys

-- mkOrganisation

-- mkOrgName1

-- mkOrgName2

-- mkOrgName3

IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'OrganisationName')

BEGIN

SET @mkOrgNameKeyString = 'dbo.clrFn_DoubleMetaphone(ISNULL(dbo.fn_NormaliseOrgName(OrganisationName, 0), ''''))'

SET @mkNormalisedOrganisation = 'dbo.fn_NormaliseOrgName(OrganisationName, 0)'

SET @mkOrgName1SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.fn_NormaliseOrgName(OrganisationName, 1))'

SET @mkOrgName2SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.fn_NormaliseOrgName(OrganisationName, 2))'

SET @mkOrgName3SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.fn_NormaliseOrgName(OrganisationName, 3))'

END

IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Postcode')

BEGIN

SET @mkPostInSELECTString = 'dbo.fn_PostCodeSplitter(Postcode, 1)'

SET @mkPostOutSELECTString = 'dbo.fn_PostCodeSplitter(Postcode, 2)'

END

IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Address1')

BEGIN

IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Buildingname')

BEGIN

SET @mkPhoneticStreetSELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.fnStripString(Buildingname + '' '' + Address1))'

SET @CROSSAPPLY = ' CROSS APPLY dbo.GetNumbersFromText(isnull(Buildingname, '''') + '' '' + Address1) AS a '

SET @mkPremiseSELECTString = 'a.number'

END

ELSE

BEGIN

SET @mkPhoneticStreetSELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.fnStripString(Address1))'

SET @CROSSAPPLY = ' CROSS APPLY dbo.GetNumbersFromText(Address1) AS a '

SET @mkPremiseSELECTString = 'a.number'

END

END

IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Town')

BEGIN

SET @mkTownSELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.fnStripString(Town))'

END

SET @InsertIntoPart = 'INSERT INTO dbo.' + @SourceTable + '_keys_ (ID, GUID, mkNameKey, mkName1, mkName2, mkName3, mkNormalizedName, mkOrganizationKey,

mkNormalizedOrganization, mkOrgName1, mkOrgName2, mkorgName3, mkPostIn, mkPostOut, mkPhoneticStreet, mkPremise, mkPhoneticTown)'

---- some more processing on the keys before we insert into the keys table

set @mkNameKeySELECTString = 'CASE WHEN LEN(replace(' + @mkNameKeySELECTString + ', '' '', '''')) >=8 THEN LEFT(replace(' + @mkNameKeySELECTString + ', '' '', ''''), 8) ELSE replace(' + @mkNameKeySELECTString + ', '' '', '''') END'

set @mkName1SELECTString = 'CASE WHEN LEN(replace(' + @mkName1SELECTString + ', '' '', '''')) >=8 THEN LEFT(replace(' + @mkName1SELECTString + ', '' '', ''''), 8) ELSE replace(' + @mkName1SELECTString + ', '' '', '''') END'

set @mkName2SELECTString = 'CASE WHEN LEN(replace(' + @mkName2SELECTString + ', '' '', '''')) >=8 THEN LEFT(replace(' + @mkName2SELECTString + ', '' '', ''''), 8) ELSE replace(' + @mkName2SELECTString + ', '' '', '''') END'

set @mkName3SELECTString = 'CASE WHEN LEN(replace(' + @mkName3SELECTString + ', '' '', '''')) >=8 THEN LEFT(replace(' + @mkName3SELECTString + ', '' '', ''''), 8) ELSE replace(' + @mkName3SELECTString + ', '' '', '''') END'

set @mkPhoneticStreetSELECTString = 'CASE WHEN LEN(replace(' + @mkPhoneticStreetSELECTString + ', '' '', '''')) >=8 THEN LEFT(replace(' + @mkPhoneticStreetSELECTString + ', '' '', ''''), 8) ELSE replace(' + @mkPhoneticStreetSELECTString + ', '' '', '''') END'

set @mkTownSELECTString = 'CASE WHEN LEN(replace(' + @mkTownSELECTString + ', '' '', '''')) >=8 THEN LEFT(replace(' + @mkTownSELECTString + ', '' '', ''''), 8) ELSE replace(' + @mkTownSELECTString + ', '' '', '''') END'

SET @SelectPart = 'SELECT ' + @SourceTableUniqueReference + ', ' + @mkNameKeySELECTString + ', ' +

@mkNameKeySELECTString + ', ' + @mkName2SELECTString + ', ' + @mkName3SELECTString + ',' + @mkNormalisedName + ','

+ @mkOrgNameKeyString + ', ' + @mkNormalisedOrganisation +

', ' + @mkOrgName1SELECTString + ', ' + @mkOrgName2SELECTString + ', ' + @mkOrgName3SELECTString + ', ' + @mkPostInSELECTString + ', ' + @mkPostOutSELECTString +

', ' + @mkPhoneticStreetSELECTString + ', ' + @mkPremiseSELECTString + ', ' + @mkTownSELECTString

SET @FromPart = 'FROM Snapshot_MeritDirect_Load.dbo.' + @SourceTable

EXEC (@InsertIntoPart + ' ' + @SelectPart + ' ' + @FromPart + @CROSSAPPLY)

SET @SQL = ''

SET @SQL = 'UPDATE ' + @SourceTable + '_keys_' + ' SET mkPremise = '''' WHERE mkPremise = ''0'''

EXEC (@SQL)

SET @SQL = ''

SET @SQL = 'UPDATE ' + @SourceTable + '_keys_' + ' SET mkPremise = '''' WHERE mkPremise IS NULL'

EXEC (@SQL)

END;