USP_DATALIST_MKTFINDERNUMBERLOOKUP
A datalist containing biographical and marketing effort information for a finder number.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @FINDERNUMBER | bigint | IN | Finder number |
| @RECORDSOURCEID | uniqueidentifier | IN | Record source ID |
Definition
Copy
CREATE procedure dbo.[USP_DATALIST_MKTFINDERNUMBERLOOKUP]
(
@FINDERNUMBER bigint,
@RECORDSOURCEID uniqueidentifier
)
as
set nocount on;
-- This table represents the structure returned by this SP - it is not used in this SP.
-- It is used in the following places:
-- * Constituent.Catalog\USP_ACQUISITIONLIST_ADDMEMBER.xml.
-- * Constituent.Catalog\USP_FINDERNUMBER_ADDMEMBER.xml
-- * Blackbaud.AppFx.BBNCSvc\Catalog\Batch\USP_BBNC_BATCH_FINDERNUMBERLOOKUP.xml
-- * Marketing.Integration\BatchIntegration.vb plug-in code.
-- IF YOU ADD TO OR CHANGE THIS STRUCTURE YOU MUST UPDATE CODE IN THOSE PLACES AS WELL
--
--declare table (
-- [ID] nvarchar(36),
-- [CONSTITUENTID] nvarchar(36),
-- [FULLNAME] nvarchar(255),
-- [FIRSTNAME] nvarchar(255),
-- [MIDDLENAME] nvarchar(255),
-- [LASTNAME] nvarchar(255),
-- [TITLE] nvarchar(255),
-- [SUFFIX] nvarchar(255),
-- [COUNTRY] nvarchar(255),
-- [ADDRESSLINE1] nvarchar(255),
-- [ADDRESSLINE2] nvarchar(255),
-- [ADDRESSLINE3] nvarchar(255),
-- [ADDRESSLINE4] nvarchar(255),
-- [ADDRESSLINE5] nvarchar(255),
-- [CITY] nvarchar(255),
-- [STATE] nvarchar(255),
-- [POSTCODE] nvarchar(255),
-- [CART] nvarchar(255),
-- [DPC] nvarchar(255),
-- [LOT] nvarchar(255),
-- [PHONENUMBER] nvarchar(255),
-- [EMAILADDRESS] nvarchar(255),
-- [MAILINGIDINTEGER] int,
-- [MAILDATE] datetime,
-- [SOURCECODE] nvarchar(50),
-- [APPEALSYSTEMID] nvarchar(36),
-- [APPEALID] nvarchar(100),
-- [APPEALDESCRIPTION] nvarchar(100),
-- [PACKAGECODE] nvarchar(10),
-- [PACKAGENAME] nvarchar(100),
-- [PACKAGEDESCRIPTION] nvarchar(255),
-- [SEGMENTID] uniqueidentifier,
-- [SEGMENTNAME] nvarchar(203),
-- [LISTNAME] nvarchar(100),
-- [TITLE2] nvarchar(255),
-- [SUFFIX2] nvarchar(255)
--);
/* FINDER NUMBER LOOKUP */
-- the range of finder numbers need to be added to MKTSEGMENATION table when
-- the finder file is imported
/* FINDER NUMBER LOOKUP */
-- Find the mailing for this finder number
declare @MAILINGID uniqueidentifier = dbo.[UFN_MKTSEGMENTATIONFINDERNUMBER_GETSEGMENTATION](@FINDERNUMBER);
declare @MAILINGIDINTEGER int;
declare @MAILDATE datetime;
declare @APPEALSYSTEMID nvarchar(36);
declare @APPEALID nvarchar(100);
declare @APPEALDESCRIPTION nvarchar(255);
declare @ISBBEC bit = dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@RECORDSOURCEID);
select
@MAILINGIDINTEGER = [MKTSEGMENTATION].[IDINTEGER],
@MAILDATE = isnull([MKTSEGMENTATION].[MAILDATE], [MKTSEGMENTATION].[ACTIVATEDATE]),
@APPEALSYSTEMID = [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID],
@APPEALID = (case when @ISBBEC = 1 and [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] <> '' then (select [NAME] from dbo.[APPEAL] where [ID] = cast([MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] as uniqueidentifier)) else [MKTSEGMENTATIONACTIVATE].[APPEALID] end),
@APPEALDESCRIPTION = (case when @ISBBEC = 1 and [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] <> '' then (select [DESCRIPTION] from dbo.[APPEAL] where [ID] = cast([MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] as uniqueidentifier)) else [MKTSEGMENTATIONACTIVATE].[APPEALDESCRIPTION] end)
from dbo.[MKTSEGMENTATION]
inner join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = @RECORDSOURCEID
where [MKTSEGMENTATION].[ID] = @MAILINGID
and [MKTSEGMENTATION].[ACTIVE] = 1;
--Either the mailing was not found or it has not been activated yet, so don't return any results...
if @MAILINGIDINTEGER is null
return 0;
declare @SQL nvarchar(max);
declare @PARAMS nvarchar(max);
declare @DONORQUERYVIEWCATALOGID uniqueidentifier;
declare @RECORDSOURCEID_INTERNAL uniqueidentifier;
declare @PRIMARYKEYFIELD nvarchar(128);
declare @DONORID nvarchar(36);
declare @CONSTITUENTID nvarchar(36);
declare @SEGMENTID uniqueidentifier;
declare @TESTSEGMENTID uniqueidentifier;
declare @SEGMENTNAME nvarchar(203);
declare @SOURCECODE nvarchar(50);
/* FINDER NUMBER LOOKUP */
-- This will return the name of the Finder File table used for this mailing. If no
-- finder file has been imported, then this comes back as null. We'll check the finder file first for
-- the record; if that fails then we can look in the @ACTIVATEDMAILINGTABLE
--
-- When we import a Finder File, we're also going to have update the MKTSOURCECODEMAP table. That will
-- contain the mailing ID, source code, segmentation segment id, and segmentation test segment id.
-- We'll join the MKTSOURCECODEMAP table to the MKTFINDERFILE_<guid> table on SOURCE CODE so we can
-- get at the SEGMENT information.
--
-- We also need to add the range of Finder Numbers to the Segmentation table so we can locate the mailing and
-- grab the MKTSEGMENTATION.ID
--
declare @USEFINDERFILE bit;
declare @FINDERFILETABLE nvarchar(128) = dbo.[UFN_MKTFINDERFILE_BUILDTABLENAME](@MAILINGID);
declare @FINDERFILEHASCONSTITUENTID bit = 0;
if exists(select 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @FINDERFILETABLE)
begin
select
@PRIMARYKEYFIELD = [PRIMARYKEYFIELD]
from dbo.[QUERYVIEWCATALOG]
where [ID] = @RECORDSOURCEID;
--If they import multiple finder files and import the same FINDERNUMBER multiple times with different names, segments, or sourcecodes,
--then we need to make sure we only grab info for the findernumber from the latest import file.
set @SQL = 'with [RANKEDIMPORT] ([ID], [RANK]) as' + char(13) +
'(' + char(13) +
' select' + char(13) +
' [FT].[ID],' + char(13) +
' row_number() over (partition by [FT].[FINDERNUMBER] order by [MKTFINDERFILEIMPORTPROCESS].[DATEADDED] desc) as [RANK]' + char(13) +
' from dbo.[' + @FINDERFILETABLE + '] as [FT] with (index([IX_' + @FINDERFILETABLE + '_FINDERNUMBER]))' + char(13) +
' inner join dbo.[MKTFINDERFILEIMPORTPROCESS] on [MKTFINDERFILEIMPORTPROCESS].[ID] = [FT].[FINDERFILEID]' + char(13) +
' where [FT].[FINDERNUMBER] = @FINDERNUMBER' + char(13) +
')' + char(13) +
'select' + char(13) +
' @DONORQUERYVIEWCATALOGID = [SC].[DONORQUERYVIEWCATALOGID],' + char(13) +
' @DONORID = cast([FT].[ID] as nvarchar(36)),' + char(13);
--If the finder file table contains a field mapped to the record source primary key, then pull
--that field too because that will indicate a constituent instead of a list person...
if exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @FINDERFILETABLE and [COLUMN_NAME] = @PRIMARYKEYFIELD)
set @SQL += ' @CONSTITUENTID = cast([FT].[' + @PRIMARYKEYFIELD + '] as nvarchar(36)),' + char(13);
else
set @SQL += ' @CONSTITUENTID = null,' + char(13);
set @SQL += ' @SEGMENTID = [SC].[SEGMENTATIONSEGMENTID],' + char(13) +
' @TESTSEGMENTID = [SC].[SEGMENTATIONTESTSEGMENTID],' + char(13) +
' @SOURCECODE = [FT].[SOURCECODE]' + char(13) +
'from [RANKEDIMPORT]' + char(13) +
'inner join dbo.[' + @FINDERFILETABLE + '] as [FT] on [FT].[ID] = [RANKEDIMPORT].[ID]' + char(13) +
'inner join dbo.[MKTSOURCECODEMAP] as [SC] on [FT].[SOURCECODE] = [SC].[SOURCECODE] and [SC].[SEGMENTATIONID] = @MAILINGID' + char(13) +
'where [RANKEDIMPORT].[RANK] = 1';
set @PARAMS = '@MAILINGID uniqueidentifier, @FINDERNUMBER bigint, @DONORQUERYVIEWCATALOGID uniqueidentifier output, @DONORID nvarchar(36) output, @CONSTITUENTID nvarchar(36) output, @SEGMENTID uniqueidentifier output, @TESTSEGMENTID uniqueidentifier output, @SOURCECODE nvarchar(50) output';
exec sp_executesql @SQL, @PARAMS, @MAILINGID = @MAILINGID, @FINDERNUMBER = @FINDERNUMBER, @DONORQUERYVIEWCATALOGID = @DONORQUERYVIEWCATALOGID output, @DONORID = @DONORID output, @CONSTITUENTID = @CONSTITUENTID output, @SEGMENTID = @SEGMENTID output, @TESTSEGMENTID = @TESTSEGMENTID output, @SOURCECODE = @SOURCECODE output;
set @USEFINDERFILE = 1;
set @RECORDSOURCEID_INTERNAL = @DONORQUERYVIEWCATALOGID;
set @FINDERFILEHASCONSTITUENTID = (case when ltrim(rtrim(isnull(@CONSTITUENTID, ''))) = '' then 0 else 1 end);
end
else
set @USEFINDERFILE = 0;
/* FINDER NUMBER LOOKUP */
-- if we didn't find the donor based on what's in the finder file, then try the activation table
if @DONORID is null
begin
-- Find the activated mailing table for the mailing
declare @ACTIVATEDMAILINGTABLE nvarchar(128);
set @ACTIVATEDMAILINGTABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@MAILINGID);
set @SQL = 'select' + char(13) +
' @DONORQUERYVIEWCATALOGID = [DONORQUERYVIEWCATALOGID],' + char(13) +
' @DONORID = cast([DONORID] as nvarchar(36)),' + char(13) +
' @SEGMENTID = [SEGMENTID],' + char(13) +
' @TESTSEGMENTID = [TESTSEGMENTID],' + char(13) +
' @SOURCECODE = [SOURCECODE]' + char(13) +
'from dbo.[' + @ACTIVATEDMAILINGTABLE + ']' + char(13) +
'where [FINDERNUMBER] = @FINDERNUMBER';
set @PARAMS = '@FINDERNUMBER bigint, @DONORQUERYVIEWCATALOGID uniqueidentifier output, @DONORID nvarchar(36) output, @SEGMENTID uniqueidentifier output, @TESTSEGMENTID uniqueidentifier output, @SOURCECODE nvarchar(50) output';
exec sp_executesql @SQL, @PARAMS, @FINDERNUMBER = @FINDERNUMBER, @DONORQUERYVIEWCATALOGID = @DONORQUERYVIEWCATALOGID output, @DONORID = @DONORID output, @SEGMENTID = @SEGMENTID output, @TESTSEGMENTID = @TESTSEGMENTID output, @SOURCECODE = @SOURCECODE output;
set @USEFINDERFILE = 0;
end;
-- Grab the package information for the segment
declare @PACKAGECODE nvarchar(10);
declare @PACKAGENAME nvarchar(100);
declare @PACKAGEDESCRIPTION nvarchar(255);
if @TESTSEGMENTID is not null
select
@PACKAGECODE = [MKTSEGMENTATIONPACKAGE].[CODE],
@PACKAGENAME = [MKTPACKAGE].[NAME],
@PACKAGEDESCRIPTION = [MKTPACKAGE].[DESCRIPTION],
@SEGMENTNAME = dbo.[UFN_MKTSEGMENTATIONTESTSEGMENT_GETNAME]([MKTSEGMENTATIONTESTSEGMENT].[ID])
from dbo.[MKTSEGMENTATIONTESTSEGMENT]
inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
inner join dbo.[MKTSEGMENTATIONPACKAGE] on [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = [MKTPACKAGE].[ID] and [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = @MAILINGID
where [MKTSEGMENTATIONTESTSEGMENT].[ID] = @TESTSEGMENTID;
else
select
@PACKAGECODE = [MKTSEGMENTATIONPACKAGE].[CODE],
@PACKAGENAME = [MKTPACKAGE].[NAME],
@PACKAGEDESCRIPTION = [MKTPACKAGE].[DESCRIPTION],
@SEGMENTNAME = [MKTSEGMENT].[NAME]
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
inner join dbo.[MKTSEGMENTATIONPACKAGE] on [MKTSEGMENTATIONPACKAGE].[PACKAGEID] = [MKTPACKAGE].[ID] and [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = @MAILINGID
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID;
declare @LISTNAME nvarchar(100);
if @USEFINDERFILE = 0
begin
if exists(select [ID] from dbo.[MKTSEGMENTLIST] where [QUERYVIEWCATALOGID] = @DONORQUERYVIEWCATALOGID)
begin
-- This finder number came from a list, so get the record source for the list
select
@RECORDSOURCEID_INTERNAL = [MKTLIST].[RECORDSOURCEID],
@LISTNAME = [MKTLIST].[NAME]
from dbo.[MKTSEGMENTLIST]
inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
where [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] = @DONORQUERYVIEWCATALOGID;
-- Check if this person has already been matched back for this list. If they have, then return their record source donor ID as well.
-- Do not add a SEGMENTATIONID filter to this. If the list person has ever been matched back to any mailing, then use that ID.
-- If they've been matched back, then grab their matched back ID.
set @SQL = 'select' + char(13) +
' @CONSTITUENTID = cast([DONORID] as nvarchar(36))' + char(13) +
'from dbo.[' + dbo.[UFN_MKTRECORDSOURCE_MAKEMATCHBACKTABLENAME](@RECORDSOURCEID_INTERNAL) + ']' + char(13) +
'where [ID] = @DONORID';
set @PARAMS = '@DONORID nvarchar(36), @CONSTITUENTID nvarchar(36) output';
exec sp_executesql @SQL, @PARAMS, @DONORID = @DONORID, @CONSTITUENTID = @CONSTITUENTID output;
end
else
begin
-- This finder number came from a record source
set @RECORDSOURCEID_INTERNAL = @DONORQUERYVIEWCATALOGID;
set @CONSTITUENTID = @DONORID;
end
end
-- If the record source we found for the finder number does not match the record source passed in, then this finder number
-- belongs to a different set of data, so don't return any information.
/* FINDER NUMBER LOOKUP */
-- It'll fall in here too for Finder File Lookup - having failed the List test.
/* FINDER NUMBER LOOKUP */
if @RECORDSOURCEID_INTERNAL = @RECORDSOURCEID
begin
declare @QUERYVIEWOBJECTNAME nvarchar(128);
/* FINDER NUMBER LOOKUP */
-- the Finder File table will share field names with the parent query view,
-- we just need to swap out the PRIMARK KEY FIELD (always ID in the FINDER FILE) and
-- the QUERYVIEWOBJECTNAME, which should be built by dbo.[UFN_MKTFINDERFILE_BUILDTABLENAME](@MAILINGID)
if @USEFINDERFILE=0
select
@QUERYVIEWOBJECTNAME = [OBJECTNAME],
@PRIMARYKEYFIELD = [PRIMARYKEYFIELD]
from dbo.[QUERYVIEWCATALOG]
where [ID] = @DONORQUERYVIEWCATALOGID;
else
begin
if @FINDERFILEHASCONSTITUENTID = 1
--If we have a constituent ID, then pull the info from the record source query view...
select
@QUERYVIEWOBJECTNAME = [OBJECTNAME],
@PRIMARYKEYFIELD = [PRIMARYKEYFIELD]
from dbo.[QUERYVIEWCATALOG]
where [ID] = @DONORQUERYVIEWCATALOGID;
else
begin
--If we don't have a constituent ID, then pull the info from the finder file table...
set @QUERYVIEWOBJECTNAME = @FINDERFILETABLE;
set @PRIMARYKEYFIELD = 'ID';
end
set @LISTNAME = 'finder file';
end;
/* FINDER NUMBER LOOKUP */
declare @FULLNAMEFIELD nvarchar(255);
declare @FIRSTNAMEFIELD nvarchar(255);
declare @MIDDLENAMEFIELD nvarchar(255);
declare @LASTNAMEFIELD nvarchar(255);
declare @TITLEFIELD nvarchar(255);
declare @SUFFIXFIELD nvarchar(255);
declare @COUNTRYFIELD nvarchar(255);
declare @ADDRESSLINE1FIELD nvarchar(255);
declare @ADDRESSLINE2FIELD nvarchar(255);
declare @ADDRESSLINE3FIELD nvarchar(255);
declare @ADDRESSLINE4FIELD nvarchar(255);
declare @ADDRESSLINE5FIELD nvarchar(255);
declare @CITYFIELD nvarchar(255);
declare @STATEFIELD nvarchar(255);
declare @POSTCODEFIELD nvarchar(255);
declare @CARTFIELD nvarchar(255);
declare @DPCFIELD nvarchar(255);
declare @LOTFIELD nvarchar(255);
declare @PHONENUMBERFIELD nvarchar(255);
declare @EMAILADDRESSFIELD nvarchar(255);
declare @TITLE2FIELD nvarchar(255);
declare @SUFFIX2FIELD nvarchar(255);
-- Get the field names for the mapped record source fields
select
@FULLNAMEFIELD = [FULLNAMEFIELD],
@FIRSTNAMEFIELD = [FIRSTNAMEFIELD],
@MIDDLENAMEFIELD = [MIDDLENAMEFIELD],
@LASTNAMEFIELD = [LASTNAMEFIELD],
@TITLEFIELD = [TITLEFIELD],
@SUFFIXFIELD = [SUFFIXFIELD],
@COUNTRYFIELD = [COUNTRYFIELD],
@ADDRESSLINE1FIELD = [ADDRESSLINE1FIELD],
@ADDRESSLINE2FIELD = [ADDRESSLINE2FIELD],
@ADDRESSLINE3FIELD = [ADDRESSLINE3FIELD],
@ADDRESSLINE4FIELD = [ADDRESSLINE4FIELD],
@ADDRESSLINE5FIELD = [ADDRESSLINE5FIELD],
@CITYFIELD = [CITYFIELD],
@STATEFIELD = [STATEFIELD],
@POSTCODEFIELD = [POSTCODEFIELD],
@CARTFIELD = [CARTFIELD],
@DPCFIELD = [DPCFIELD],
@LOTFIELD = [LOTFIELD],
@PHONENUMBERFIELD = [PHONENUMBERFIELD],
@EMAILADDRESSFIELD = [EMAILADDRESSFIELD],
@TITLE2FIELD = [TITLE2FIELD],
@SUFFIX2FIELD = [SUFFIX2FIELD]
from dbo.[MKTRECORDSOURCEFIELDMAPPINGS]
where [ID] = @RECORDSOURCEID_INTERNAL;
set @CONSTITUENTID = isnull(@CONSTITUENTID, '');
set @SOURCECODE = isnull(@SOURCECODE, '');
set @APPEALSYSTEMID = isnull(@APPEALSYSTEMID, '');
set @APPEALID = isnull(@APPEALID, '');
set @APPEALDESCRIPTION = isnull(@APPEALDESCRIPTION, '');
set @PACKAGECODE = isnull(@PACKAGECODE, '');
set @PACKAGENAME = isnull(@PACKAGENAME, '');
set @PACKAGEDESCRIPTION = isnull(@PACKAGEDESCRIPTION, '');
set @SEGMENTID = isnull(@TESTSEGMENTID, @SEGMENTID);
set @SEGMENTNAME = isnull(@SEGMENTNAME, '');
set @LISTNAME = isnull(@LISTNAME, '');
-- Build the select statement to grab the bio information for this finder number based on the fields that actually exist in the query view.
set @SQL = 'select' + char(13) +
' ' + (case when @FINDERFILEHASCONSTITUENTID = 1 then '@DONORID' else '[' + @PRIMARYKEYFIELD + ']' end) + ' as [ID],' + char(13) +
' @CONSTITUENTID as [CONSTITUENTID],' + char(13);
if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = @FULLNAMEFIELD)
set @SQL = @SQL + ' [' + @FULLNAMEFIELD + '] as [FULLNAME],' + char(13);
else
set @SQL = @SQL + ' null as [FULLNAME],' + char(13);
if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = @FIRSTNAMEFIELD)
set @SQL = @SQL + ' [' + @FIRSTNAMEFIELD + '] as [FIRSTNAME],' + char(13);
else
set @SQL = @SQL + ' null as [FIRSTNAME],' + char(13);
if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = @MIDDLENAMEFIELD)
set @SQL = @SQL + ' [' + @MIDDLENAMEFIELD + '] as [MIDDLENAME],' + char(13);
else
set @SQL = @SQL + ' null as [MIDDLENAME],' + char(13);
if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = @LASTNAMEFIELD)
set @SQL = @SQL + ' [' + @LASTNAMEFIELD + '] as [LASTNAME],' + char(13);
else
set @SQL = @SQL + ' null as [LASTNAME],' + char(13);
if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = @TITLEFIELD)
set @SQL = @SQL + ' [' + @TITLEFIELD + '] as [TITLE],' + char(13);
else
set @SQL = @SQL + ' null as [TITLE],' + char(13);
if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = @SUFFIXFIELD)
set @SQL = @SQL + ' [' + @SUFFIXFIELD + '] as [SUFFIX],' + char(13);
else
set @SQL = @SQL + ' null as [SUFFIX],' + char(13);
if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = @COUNTRYFIELD)
set @SQL = @SQL + ' [' + @COUNTRYFIELD + '] as [COUNTRY],' + char(13);
else
set @SQL = @SQL + ' null as [COUNTRY],' + char(13);
if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = @ADDRESSLINE1FIELD)
set @SQL = @SQL + ' [' + @ADDRESSLINE1FIELD + '] as [ADDRESSLINE1],' + char(13);
else
set @SQL = @SQL + ' null as [ADDRESSLINE1],' + char(13);
if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = @ADDRESSLINE2FIELD)
set @SQL = @SQL + ' [' + @ADDRESSLINE2FIELD + '] as [ADDRESSLINE2],' + char(13);
else
set @SQL = @SQL + ' null as [ADDRESSLINE2],' + char(13);
if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = @ADDRESSLINE3FIELD)
set @SQL = @SQL + ' [' + @ADDRESSLINE3FIELD + '] as [ADDRESSLINE3],' + char(13);
else
set @SQL = @SQL + ' null as [ADDRESSLINE3],' + char(13);
if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = @ADDRESSLINE4FIELD)
set @SQL = @SQL + ' [' + @ADDRESSLINE4FIELD + '] as [ADDRESSLINE4],' + char(13);
else
set @SQL = @SQL + ' null as [ADDRESSLINE4],' + char(13);
if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = @ADDRESSLINE5FIELD)
set @SQL = @SQL + ' [' + @ADDRESSLINE5FIELD + '] as [ADDRESSLINE5],' + char(13);
else
set @SQL = @SQL + ' null as [ADDRESSLINE5],' + char(13);
if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = @CITYFIELD)
set @SQL = @SQL + ' [' + @CITYFIELD + '] as [CITY],' + char(13);
else
set @SQL = @SQL + ' null as [CITY],' + char(13);
if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = @STATEFIELD)
set @SQL = @SQL + ' [' + @STATEFIELD + '] as [STATE],' + char(13);
else
set @SQL = @SQL + ' null as [STATE],' + char(13);
if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = @POSTCODEFIELD)
set @SQL = @SQL + ' [' + @POSTCODEFIELD + '] as [POSTCODE],' + char(13);
else
set @SQL = @SQL + ' null as [POSTCODE],' + char(13);
if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = @CARTFIELD)
set @SQL = @SQL + ' [' + @CARTFIELD + '] as [CART],' + char(13);
else
set @SQL = @SQL + ' null as [CART],' + char(13);
if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = @DPCFIELD)
set @SQL = @SQL + ' [' + @DPCFIELD + '] as [DPC],' + char(13);
else
set @SQL = @SQL + ' null as [DPC],' + char(13);
if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = @LOTFIELD)
set @SQL = @SQL + ' [' + @LOTFIELD + '] as [LOT],' + char(13);
else
set @SQL = @SQL + ' null as [LOT],' + char(13);
if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = @PHONENUMBERFIELD)
set @SQL = @SQL + ' [' + @PHONENUMBERFIELD + '] as [PHONENUMBER],' + char(13);
else
set @SQL = @SQL + ' null as [PHONENUMBER],' + char(13);
if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = @EMAILADDRESSFIELD)
set @SQL = @SQL + ' [' + @EMAILADDRESSFIELD + '] as [EMAILADDRESS],' + char(13);
else
set @SQL = @SQL + ' null as [EMAILADDRESS],' + char(13);
-- Append the mailing-related data
set @SQL = @SQL +
' @MAILINGIDINTEGER as [MAILINGIDINTEGER],' + char(13) +
' @MAILDATE as [MAILDATE],' + char(13) +
' @SOURCECODE as [SOURCECODE],' + char(13) +
' @APPEALSYSTEMID as [APPEALSYSTEMID],' + char(13) +
' @APPEALID as [APPEALID],' + char(13) +
' @APPEALDESCRIPTION as [APPEALDESCRIPTION],' + char(13) +
' @PACKAGECODE as [PACKAGECODE],' + char(13) +
' @PACKAGENAME as [PACKAGENAME],' + char(13) +
' @PACKAGEDESCRIPTION as [PACKAGEDESCRIPTION],' + char(13) +
' @SEGMENTID as [SEGMENTID],' + char(13) +
' @SEGMENTNAME as [SEGMENTNAME],' + char(13) +
' @LISTNAME as [LISTNAME],' + char(13);
--adding these fields in the middle so not to break compat on the datalist
if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = @TITLE2FIELD)
set @SQL = @SQL + ' [' + @TITLE2FIELD + '] as [TITLE2],' + char(13);
else
set @SQL = @SQL + ' null as [TITLE2],' + char(13);
if exists(select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @QUERYVIEWOBJECTNAME and [COLUMN_NAME] = @SUFFIX2FIELD)
set @SQL = @SQL + ' [' + @SUFFIX2FIELD + '] as [SUFFIX2]' + char(13);
else
set @SQL = @SQL + ' null as [SUFFIX2]' + char(13);
set @SQL += 'from dbo.[' + @QUERYVIEWOBJECTNAME + ']' + char(13) +
'where [' + @PRIMARYKEYFIELD + '] = ' + (case when @FINDERFILEHASCONSTITUENTID = 1 then '@CONSTITUENTID' else '@DONORID' end);
set @PARAMS = '@CONSTITUENTID nvarchar(36), ' +
'@MAILINGIDINTEGER int, ' +
'@MAILDATE datetime, ' +
'@SOURCECODE nvarchar(50), ' +
'@APPEALSYSTEMID nvarchar(36), ' +
'@APPEALID nvarchar(100), ' +
'@APPEALDESCRIPTION nvarchar(100), ' +
'@PACKAGECODE nvarchar(10), ' +
'@PACKAGENAME nvarchar(100), ' +
'@PACKAGEDESCRIPTION nvarchar(255), ' +
'@SEGMENTID uniqueidentifier, ' +
'@SEGMENTNAME nvarchar(203), ' +
'@LISTNAME nvarchar(100), ' +
'@DONORID nvarchar(36)';
exec sp_executesql @SQL, @PARAMS,
@CONSTITUENTID = @CONSTITUENTID,
@MAILINGIDINTEGER = @MAILINGIDINTEGER,
@MAILDATE = @MAILDATE,
@SOURCECODE = @SOURCECODE,
@APPEALSYSTEMID = @APPEALSYSTEMID,
@APPEALID = @APPEALID,
@APPEALDESCRIPTION = @APPEALDESCRIPTION,
@PACKAGECODE = @PACKAGECODE,
@PACKAGENAME = @PACKAGENAME,
@PACKAGEDESCRIPTION = @PACKAGEDESCRIPTION,
@SEGMENTID = @SEGMENTID,
@SEGMENTNAME = @SEGMENTNAME,
@LISTNAME = @LISTNAME,
@DONORID = @DONORID;
end;
return 0;