InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 23:46:53 GMT20http://www.sqlservercentral.com/Forums/Topic1415963-391-1.aspxAs requested here is the complete sproc:[code="sql"]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;[/code]Fri, 08 Feb 2013 02:56:44 GMTAbu Dinahttp://www.sqlservercentral.com/Forums/Topic1415963-391-1.aspx[quote][b]demonfox (2/7/2013)[/b][hr][quote][b]Abu Dina (2/7/2013)[/b][hr]Fair enough. I think I'm trying to be too clever when I should be concentrating on getting the rest of the project done first! This part works but everytime I look at all the IFs I get confused so I thought there might be a better way to achive the same result.[/quote]If you could post the code , may be we can work it out ...[/quote] +1Please post the Insert statements you are working with currently.Thu, 07 Feb 2013 21:12:51 GMTvinu512http://www.sqlservercentral.com/Forums/Topic1415963-391-1.aspx[quote][b]Abu Dina (2/7/2013)[/b][hr]Fair enough. I think I'm trying to be too clever when I should be concentrating on getting the rest of the project done first! This part works but everytime I look at all the IFs I get confused so I thought there might be a better way to achive the same result.[/quote]If you could post the code , may be we can work it out ...Thu, 07 Feb 2013 20:46:35 GMTdemonfoxhttp://www.sqlservercentral.com/Forums/Topic1415963-391-1.aspxFair enough. I think I'm trying to be too clever when I should be concentrating on getting the rest of the project done first! This part works but everytime I look at all the IFs I get confused so I thought there might be a better way to achive the same result.Thu, 07 Feb 2013 05:44:15 GMTAbu Dinahttp://www.sqlservercentral.com/Forums/Topic1415963-391-1.aspx[quote][b]vinu512 (2/7/2013)[/b][hr]So if the incoming data doesn't have a forename then what do you insert into the table??..Do you Insert NULL??....[/quote]Yes that's right, where I define the variables I have them default to = 'NULL'so if this IF the statment is not valid, i.e. no forename then the SELECT variable for the forename will be NULL.Thu, 07 Feb 2013 05:42:30 GMTAbu Dinahttp://www.sqlservercentral.com/Forums/Topic1415963-391-1.aspx[quote][b]Abu Dina (2/6/2013)[/b][hr]I seem to have many IF statmements to check for columns before I build my dynamic insert statement.Any ideas?[/quote]'If' is a conditional statement ;so, when you need it , you use it ..If that fieldMapping table has to be searched for creating dynamic sql then you can go on with it ;another way could be , since you are not executing the sql's , and I suppose using the same variable elsewhere and deciding which to execute , you can put the conditions on executing the dynamic sql rather than setting the value to variables.Thu, 07 Feb 2013 05:09:28 GMTdemonfoxhttp://www.sqlservercentral.com/Forums/Topic1415963-391-1.aspxSo if the incoming data doesn't have a forename then what do you insert into the table??..Do you Insert NULL??....I think you should read upon [url=http://msdn.microsoft.com/en-us/library/ms190349.aspx][b][u]Coalesce[/u][/b][/url]. If you still cant get it to work then get back to me here.....we'll work something out. :-)Thu, 07 Feb 2013 03:52:53 GMTvinu512http://www.sqlservercentral.com/Forums/Topic1415963-391-1.aspxNot sure if I explained this very well so I will try again!I have a table with the following columns:TABLE NAME = Phonetic_Keys[b]RecordID, PhoneticSurname, PhoneticForename, PhoneticStreet, PostCode, Premise[/b]This table will be populated from incoming data which could contain the following columns:TABLE NAME = New_Data[b]ID, Forrename, Surname, Address1, Address2, Address3, Town, PostCode[/b]If my incoming file is like above then I need to build an insert statment to populate the Phonetic_Keys table.However, the incoming file may not have a forename or a address3 so I've used a series of IF ststements to build my SELECT statment to be used to insert into the phonetic keys table.Please see code above. Is this a good way to do it?I seem to have many IF statmements to check for columns before I build my dynamic insert statement.Any ideas?Wed, 06 Feb 2013 04:58:44 GMTAbu Dinahttp://www.sqlservercentral.com/Forums/Topic1415963-391-1.aspxI have a process like this:STEP 1: Data files get loaded into a staging database.STEP 2: Phonetic Keys table get populated based on incoming data file.Now the Keys table I've designed never changes. It has the following columns (simplified):[b]RecordID, PhoneticSurname, PhoneticForename, PhoneticStreet, PostCode, Premise[/b]The incoming data file could have any of the following columns:[b]Forrename, Surname, Address1, Address2, Address3, Town, PostCode[/b]In my stored procedure which populates the keys table for the new file, I have a number of IF statements which check the column names to build the SELECT string.For example:[code="sql"]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.DoubleMetaPhone(dbo.GetLastWord(Surname) + LEFT(dbo.GetFirstWord(Forename), 1))' -- Phonetic Surname SET @mkName1SELECTString = 'dbo.DoubleMetaPhone(Surname)' -- Phonetic Forename SET @mkName2SELECTString = 'dbo.DoubleMetaPhone(dbo.GetFirstWord(Forename))' -- Phonetic Middle name or Initial SET @mkName3SELECTString = 'dbo.DoubleMetaPhone(dbo.GetSecondWord(Forename))' -- Normalised name = Surname + Forename + Middle Name SET @mkNormalisedName = 'dbo.GetLastWord(Surname) + '','' + dbo.GetFirstWord(Forename) + '','' + dbo.GetSecondWord(Forename)' END [/code]Is this an okay approach or am I over-complicating things?Tue, 05 Feb 2013 09:54:50 GMTAbu Dina