USP_MKTRECORDSOURCEMEMBER_LOAD

CREATE procedure dbo.[USP_MKTRECORDSOURCEMEMBER_LOAD]
(
  @ID nvarchar(36),
  @RECORDSOURCEID uniqueidentifier,
  @TABLEORVIEWNAME nvarchar(255),
  @PRIMARYKEYNAME nvarchar(255)
)
as
  set nocount on;

  --These represent the mapped fields

  declare @FULLNAMEFIELDNAME nvarchar(255);
  declare @FIRSTNAMEFIELDNAME nvarchar(255);
  declare @MIDDLENAMEFIELDNAME nvarchar(255);
  declare @LASTNAMEFIELDNAME nvarchar(255);
  declare @TITLEFIELDNAME nvarchar(255);
  declare @SUFFIXFIELDNAME nvarchar(255);
  declare @COUNTRYFIELDNAME nvarchar(255);
  declare @ADDRESSLINE1FIELDNAME nvarchar(255);
  declare @ADDRESSLINE2FIELDNAME nvarchar(255);
  declare @ADDRESSLINE3FIELDNAME nvarchar(255);
  declare @ADDRESSLINE4FIELDNAME nvarchar(255);
  declare @ADDRESSLINE5FIELDNAME nvarchar(255);
  declare @CITYFIELDNAME nvarchar(255);
  declare @STATEFIELDNAME nvarchar(255);
  declare @POSTCODEFIELDNAME nvarchar(255);
  declare @CARTFIELDNAME nvarchar(255);
  declare @DPCFIELDNAME nvarchar(255);
  declare @LOTFIELDNAME nvarchar(255);
  declare @PHONENUMBERFIELDNAME nvarchar(255);
  declare @EMAILADDRESSFIELDNAME nvarchar(255);
  declare @FIRSTGIFTDATEFIELDNAME nvarchar(255);

  --Grab the field names

  select
    @FULLNAMEFIELDNAME = [FULLNAMEFIELD],
    @FIRSTNAMEFIELDNAME = [FIRSTNAMEFIELD],
    @MIDDLENAMEFIELDNAME = [MIDDLENAMEFIELD],
    @LASTNAMEFIELDNAME = [LASTNAMEFIELD],
    @TITLEFIELDNAME = [TITLEFIELD],
    @SUFFIXFIELDNAME = [SUFFIXFIELD],
    @COUNTRYFIELDNAME = [COUNTRYFIELD],
    @ADDRESSLINE1FIELDNAME = [ADDRESSLINE1FIELD],
    @ADDRESSLINE2FIELDNAME = [ADDRESSLINE2FIELD],
    @ADDRESSLINE3FIELDNAME = [ADDRESSLINE3FIELD],
    @ADDRESSLINE4FIELDNAME = [ADDRESSLINE4FIELD],
    @ADDRESSLINE5FIELDNAME = [ADDRESSLINE5FIELD],
    @CITYFIELDNAME = [CITYFIELD],
    @STATEFIELDNAME = [STATEFIELD],
    @POSTCODEFIELDNAME = [POSTCODEFIELD],
    @CARTFIELDNAME = [CARTFIELD],
    @DPCFIELDNAME = [DPCFIELD],
    @LOTFIELDNAME = [LOTFIELD],
    @PHONENUMBERFIELDNAME = [PHONENUMBERFIELD],
    @EMAILADDRESSFIELDNAME = [EMAILADDRESSFIELD],
    @FIRSTGIFTDATEFIELDNAME = [FIRSTGIFTDATEFIELD]
  from dbo.[MKTRECORDSOURCEFIELDMAPPINGS]
  where [ID] = @RECORDSOURCEID;


  --See if the fields exist in the table/view and clear the name if necessary

  if exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @TABLEORVIEWNAME and [COLUMN_NAME] = @FULLNAMEFIELDNAME)
    set @FULLNAMEFIELDNAME = '[' + @FULLNAMEFIELDNAME + ']';
  else
    set @FULLNAMEFIELDNAME = '''''';

  if exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @TABLEORVIEWNAME and [COLUMN_NAME] = @FIRSTNAMEFIELDNAME)
    set @FIRSTNAMEFIELDNAME = '[' + @FIRSTNAMEFIELDNAME + ']';
  else
    set @FIRSTNAMEFIELDNAME = '''''';

  if exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @TABLEORVIEWNAME and [COLUMN_NAME] = @MIDDLENAMEFIELDNAME)
    set @MIDDLENAMEFIELDNAME = '[' + @MIDDLENAMEFIELDNAME + ']';
  else
    set @MIDDLENAMEFIELDNAME = '''''';

  if exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @TABLEORVIEWNAME and [COLUMN_NAME] = @LASTNAMEFIELDNAME)
    set @LASTNAMEFIELDNAME = '[' + @LASTNAMEFIELDNAME + ']';
  else
    set @LASTNAMEFIELDNAME = '''''';

  if exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @TABLEORVIEWNAME and [COLUMN_NAME] = @TITLEFIELDNAME) 
    set @TITLEFIELDNAME = '[' + @TITLEFIELDNAME + ']';
  else
    set @TITLEFIELDNAME = '''''';

  if exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @TABLEORVIEWNAME and [COLUMN_NAME] = @SUFFIXFIELDNAME)
    set @SUFFIXFIELDNAME = '[' + @SUFFIXFIELDNAME + ']';
  else
    set @SUFFIXFIELDNAME = '''''';

  if exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @TABLEORVIEWNAME and [COLUMN_NAME] = @COUNTRYFIELDNAME)
    set @COUNTRYFIELDNAME = '[' + @COUNTRYFIELDNAME + ']';
  else
    set @COUNTRYFIELDNAME = '''''';

  if exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @TABLEORVIEWNAME and [COLUMN_NAME] = @ADDRESSLINE1FIELDNAME)
    set @ADDRESSLINE1FIELDNAME = '[' + @ADDRESSLINE1FIELDNAME + ']';
  else
    set @ADDRESSLINE1FIELDNAME = '''''';

  if exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @TABLEORVIEWNAME and [COLUMN_NAME] = @ADDRESSLINE2FIELDNAME)
    set @ADDRESSLINE2FIELDNAME = '[' + @ADDRESSLINE2FIELDNAME + ']';
  else
    set @ADDRESSLINE2FIELDNAME = '''''';

  if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @TABLEORVIEWNAME and [COLUMN_NAME] = @ADDRESSLINE3FIELDNAME)
    set @ADDRESSLINE3FIELDNAME = '[' + @ADDRESSLINE3FIELDNAME + ']';
  else
    set @ADDRESSLINE3FIELDNAME = '''''';

  if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @TABLEORVIEWNAME and [COLUMN_NAME] = @ADDRESSLINE4FIELDNAME)
    set @ADDRESSLINE4FIELDNAME = '[' + @ADDRESSLINE4FIELDNAME + ']';
  else
    set @ADDRESSLINE4FIELDNAME = '''''';

  if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @TABLEORVIEWNAME and [COLUMN_NAME] = @ADDRESSLINE5FIELDNAME)
    set @ADDRESSLINE5FIELDNAME = '[' + @ADDRESSLINE5FIELDNAME + ']';
  else
    set @ADDRESSLINE5FIELDNAME = '''''';

  if exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @TABLEORVIEWNAME and [COLUMN_NAME] = @CITYFIELDNAME)
    set @CITYFIELDNAME = '[' + @CITYFIELDNAME + ']';
  else
    set @CITYFIELDNAME = '''''';

  if exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @TABLEORVIEWNAME and [COLUMN_NAME] = @STATEFIELDNAME)
    set @STATEFIELDNAME = '[' + @STATEFIELDNAME + ']';
  else
    set @STATEFIELDNAME = '''''';

  if exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @TABLEORVIEWNAME and [COLUMN_NAME] = @POSTCODEFIELDNAME)
    set @POSTCODEFIELDNAME = '[' + @POSTCODEFIELDNAME + ']';
  else
    set @POSTCODEFIELDNAME = '''''';

  if exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @TABLEORVIEWNAME and [COLUMN_NAME] = @CARTFIELDNAME)
    set @CARTFIELDNAME = '[' + @CARTFIELDNAME + ']';
  else
    set @CARTFIELDNAME = '''''';

  if exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @TABLEORVIEWNAME and [COLUMN_NAME] = @DPCFIELDNAME)
    set @DPCFIELDNAME = '[' + @DPCFIELDNAME + ']';
  else
    set @DPCFIELDNAME = '''''';

  if exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @TABLEORVIEWNAME and [COLUMN_NAME] = @LOTFIELDNAME)
    set @LOTFIELDNAME = '[' + @LOTFIELDNAME + ']';
  else
    set @LOTFIELDNAME = '''''';

  if exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @TABLEORVIEWNAME and [COLUMN_NAME] = @PHONENUMBERFIELDNAME)
    set @PHONENUMBERFIELDNAME = '[' + @PHONENUMBERFIELDNAME + ']';
  else
    set @PHONENUMBERFIELDNAME = '''''';

  if exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @TABLEORVIEWNAME and [COLUMN_NAME] = @EMAILADDRESSFIELDNAME)
    set @EMAILADDRESSFIELDNAME = '[' + @EMAILADDRESSFIELDNAME + ']';
  else
    set @EMAILADDRESSFIELDNAME = '''''';

  if exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @TABLEORVIEWNAME and [COLUMN_NAME] = @FIRSTGIFTDATEFIELDNAME)
    set @FIRSTGIFTDATEFIELDNAME = '[' + @FIRSTGIFTDATEFIELDNAME + ']';
  else
    set @FIRSTGIFTDATEFIELDNAME = '''''';


  --Build the SQL

  declare @SQL nvarchar(max);
  set @SQL = 'select' + char(13) +
             '  ' + @FULLNAMEFIELDNAME + ' as [FULLNAME],' + char(13) +
             '  ' + @FIRSTNAMEFIELDNAME + ' as [FIRSTNAME],' + char(13) +
             '  ' + @MIDDLENAMEFIELDNAME + ' as [MIDDLENAME],' + char(13) +
             '  ' + @LASTNAMEFIELDNAME + ' as [LASTNAME],' + char(13) +
             '  ' + @TITLEFIELDNAME + ' as [TITLE],' + char(13) +
             '  ' + @SUFFIXFIELDNAME + ' as [SUFFIX],' + char(13) +
             '  ' + @COUNTRYFIELDNAME + ' as [COUNTRY],' + char(13) +
             '  ' + @ADDRESSLINE1FIELDNAME + ' as [ADDRESSLINE1],' + char(13) +
             '  ' + @ADDRESSLINE2FIELDNAME + ' as [ADDRESSLINE2],' + char(13) +
             '  ' + @ADDRESSLINE3FIELDNAME + ' as [ADDRESSLINE3],' + char(13) +
             '  ' + @ADDRESSLINE4FIELDNAME + ' as [ADDRESSLINE4],' + char(13) +
             '  ' + @ADDRESSLINE5FIELDNAME + ' as [ADDRESSLINE5],' + char(13) +
             '  ' + @CITYFIELDNAME + ' as [CITY],' + char(13) +
             '  ' + @STATEFIELDNAME + ' as [STATE],' + char(13) +
             '  ' + @POSTCODEFIELDNAME + ' as [POSTCODE],' + char(13) +
             '  ' + @CARTFIELDNAME + ' as [CART],' + char(13) +
             '  ' + @DPCFIELDNAME + ' as [DPC],' + char(13) +
             '  ' + @LOTFIELDNAME + ' as [LOT],' + char(13) +
             '  ' + @PHONENUMBERFIELDNAME + ' as [PHONENUMBER],' + char(13) +
             '  ' + @EMAILADDRESSFIELDNAME + ' as [EMAILADDRESS],' + char(13) +
             '  ' + @FIRSTGIFTDATEFIELDNAME + ' as [FIRSTGIFTDATE]' + char(13) +
             'from dbo.[' + @TABLEORVIEWNAME + ']' + char(13) +
             'where [' + @PRIMARYKEYNAME + '] = @ID';
    exec sp_executesql @SQL, N'@ID nvarchar(36)', @ID = @ID;

  return 0;