USP_MKTSEGMENTATION_POPULATEEXCLUDEDIDSTEMPTABLE
Populates a temp table with the excluded donor IDs for a given marketing effort.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SEGMENTATIONID | uniqueidentifier | IN | |
| @EXCLUDEDIDSTEMPTABLENAME | nvarchar(128) | IN | |
| @CALLERHANDLEDINSERT | bit | IN |
Definition
Copy
CREATE procedure dbo.[USP_MKTSEGMENTATION_POPULATEEXCLUDEDIDSTEMPTABLE]
(
@SEGMENTATIONID uniqueidentifier,
@EXCLUDEDIDSTEMPTABLENAME nvarchar(128),
@CALLERHANDLEDINSERT bit = 0
)
as
set nocount on;
declare @SEGMENTID uniqueidentifier;
declare @SEGMENTNAME nvarchar(100);
declare @SEGMENTTYPECODE tinyint;
declare @SEGMENTVIEWNAME nvarchar(128);
declare @SEGMENTATIONSEGMENTID uniqueidentifier;
declare @RECORDSOURCEID uniqueidentifier;
declare @RECORDSOURCETYPE tinyint;
declare @ISBBEC bit;
declare @QUERYVIEWCATALOGID uniqueidentifier;
declare @PKDATATYPE nvarchar(128);
declare @DONORTORECIPIENTCONVERSIONTABLENAME nvarchar(128);
declare @DONORTORECIPIENTCONVERSIONDONORIDFIELDNAME nvarchar(255);
declare @DONORTORECIPIENTCONVERSIONRECIPIENTIDFIELDNAME nvarchar(255);
declare @NEEDDISTINCT bit = 0;
declare @EFFORTTYPECODE tinyint;
declare @DATATABLE nvarchar(128);
declare @USECONSTITUENTFILEIMPORTTABLE bit = 0;
declare @CONSTITUENTFILEIMPORTTABLE nvarchar(128);
declare @SQL nvarchar(max);
declare @FULLNAMEFIELDNAME nvarchar(max); --this needs to be more than 255
declare @FIRSTNAMEFIELDNAME nvarchar(255);
declare @MIDDLENAMEFIELDNAME nvarchar(255);
declare @LASTNAMEFIELDNAME nvarchar(255);
declare @ORGNAMEFIELDNAME nvarchar(255);
declare @PARENTTABLENAME nvarchar(128);
declare @PARENTID nvarchar(128);
begin try
select
@EFFORTTYPECODE = [MAILINGTYPECODE],
@DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([ID]),
@CONSTITUENTFILEIMPORTTABLE = dbo.[UFN_MKTCONSTITUENTFILEIMPORT_BUILDTABLENAME]([ID])
from dbo.[MKTSEGMENTATION]
where [ID] = @SEGMENTATIONID;
if @EFFORTTYPECODE = 0 and exists (select top 1 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @CONSTITUENTFILEIMPORTTABLE)
set @USECONSTITUENTFILEIMPORTTABLE = 1;
if @CALLERHANDLEDINSERT = 0
begin
/* Insert excluded IDs into a temp table for each segment in the effort. */
declare SEGMENTCURSOR cursor local fast_forward for
select
[MKTSEGMENTATIONSEGMENT].[ID] as [SEGMENTATIONSEGMENTID],
[MKTSEGMENT].[ID] as [SEGMENTID],
[MKTSEGMENT].[NAME] as [SEGMENTNAME],
[MKTSEGMENT].[SEGMENTTYPECODE],
dbo.[UFN_MKTSELECTION_GETFUNCTIONNAME]([MKTSEGMENT].[IDSETREGISTERID]) as [SEGMENTVIEWNAME],
[MKTSEGMENT].[QUERYVIEWCATALOGID] as [RECORDSOURCEID],
dbo.[UFN_MKTSEGMENT_GETMARKETINGRECORDTYPE]([MKTSEGMENT].[ID]) as [RECORDSOURCETYPE],
convert(bit, case when dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTSEGMENT].[QUERYVIEWCATALOGID]) = 1 then 1 else 0 end)
from dbo.[MKTSEGMENTATIONSEGMENT]
inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
and [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0
order by [MKTSEGMENTATIONSEGMENT].[SEQUENCE];
open SEGMENTCURSOR;
fetch next from SEGMENTCURSOR into @SEGMENTATIONSEGMENTID, @SEGMENTID, @SEGMENTNAME, @SEGMENTTYPECODE, @SEGMENTVIEWNAME, @RECORDSOURCEID, @RECORDSOURCETYPE, @ISBBEC;
while (@@FETCH_STATUS = 0)
begin
/* Get the parent table name and primary ID field name. */
if @RECORDSOURCETYPE = 1 --Record Source
select
@QUERYVIEWCATALOGID = [QUERYVIEWCATALOG].[ID],
@PKDATATYPE = [QUERYVIEWCATALOG].[PRIMARYKEYTYPENAME]
from dbo.[MKTSEGMENT]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
where [MKTSEGMENT].[ID] = @SEGMENTID;
else
begin
if @RECORDSOURCETYPE = 2 --List or duplicate list
select
@QUERYVIEWCATALOGID = [QUERYVIEWCATALOG].[ID],
@PKDATATYPE = [QUERYVIEWCATALOG].[PRIMARYKEYTYPENAME]
from dbo.[MKTSEGMENT]
inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
left join dbo.[MKTSEGMENT] as [PARENTSEGMENT] on [PARENTSEGMENT].[ID] = [MKTSEGMENTLIST].[PARENTSEGMENTID]
left join dbo.[MKTSEGMENTLIST] as [PARENTSEGMENTLIST] on [PARENTSEGMENTLIST].[ID] = [PARENTSEGMENT].[CURRENTSEGMENTLISTID]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] or [QUERYVIEWCATALOG].[ID] = [PARENTSEGMENTLIST].[QUERYVIEWCATALOGID]
where [MKTSEGMENT].[ID] = @SEGMENTID;
else
begin
if @RECORDSOURCETYPE = 3 --Consolidated
select
@QUERYVIEWCATALOGID = [CQV].[ID],
@PKDATATYPE = [CQV].[PRIMARYKEYTYPENAME]
from dbo.[MKTSEGMENT]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
inner join dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC] on [MKTCONSOLIDATEDQUERYVIEWSPEC].[ID] = [QUERYVIEWCATALOG].[ID]
inner join dbo.[QUERYVIEWCATALOG] as [CQV] on [CQV].[ID] = [MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID]
where [MKTSEGMENT].[ID] = @SEGMENTID;
else
raiserror('Segment contains an invalid selection type.', 13, 1);
end
end
if @EFFORTTYPECODE = 1 -- acknowledgement
begin
select
@NEEDDISTINCT = 1, --needed because the same DONORID could be in the set of records more than once
-- if non-acknowledgement selections are used in the mailing's universe or exclusions, the following information
-- is used to turn them into sponsorships
@DONORTORECIPIENTCONVERSIONTABLENAME = case when @ISBBEC = 1 then 'FINANCIALTRANSACTION' else [QUERYVIEWCATALOG].[OBJECTNAME] end,
@DONORTORECIPIENTCONVERSIONDONORIDFIELDNAME = case when @ISBBEC = 1 then 'CONSTITUENTID' else [MKTGIFTRECORDSOURCE].[DONORIDFIELD] end,
@DONORTORECIPIENTCONVERSIONRECIPIENTIDFIELDNAME = case when @ISBBEC = 1 then 'ID' else [QUERYVIEWCATALOG].[PRIMARYKEYFIELD] end
from dbo.[MKTGIFTRECORDSOURCE]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTGIFTRECORDSOURCE].[QUERYVIEWCATALOGID]
where [MKTGIFTRECORDSOURCE].[ID] = @RECORDSOURCEID;
end
else if @EFFORTTYPECODE = 2 -- membership
begin
select
@NEEDDISTINCT = 1, --needed because the same DONORID could be in the set of records more than once
-- if non-membership selections are used in the mailing's universe or exclusions, the following information
-- is used to turn them into memberships
@DONORTORECIPIENTCONVERSIONTABLENAME = case when @ISBBEC = 1 then 'MEMBER' else [QUERYVIEWCATALOG].[OBJECTNAME] end,
@DONORTORECIPIENTCONVERSIONDONORIDFIELDNAME = case when @ISBBEC = 1 then 'CONSTITUENTID' else [MKTMEMBERSHIPRECORDSOURCE].[MEMBERIDFIELD] end,
@DONORTORECIPIENTCONVERSIONRECIPIENTIDFIELDNAME = case when @ISBBEC = 1 then 'MEMBERSHIPID' else [MKTMEMBERSHIPRECORDSOURCE].[MEMBERSHIPSYSTEMIDFIELD] end
from dbo.[MKTMEMBERSHIPRECORDSOURCE]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTMEMBERSHIPRECORDSOURCE].[QUERYVIEWCATALOGID]
where [MKTMEMBERSHIPRECORDSOURCE].[ID] = @RECORDSOURCEID;
end
else if @EFFORTTYPECODE = 3 -- sponsorship
begin
select
@NEEDDISTINCT = 1, --needed because the same DONORID could be in the set of records more than once
-- if non-sponsorship selections are used in the mailing's universe or exclusions, the following information
-- is used to turn them into sponsorships
@DONORTORECIPIENTCONVERSIONTABLENAME = case when @ISBBEC = 1 then 'SPONSORSHIP' else [QUERYVIEWCATALOG].[OBJECTNAME] end,
@DONORTORECIPIENTCONVERSIONDONORIDFIELDNAME = case when @ISBBEC = 1 then 'CONSTITUENTID' else [MKTSPONSORSHIPRECORDSOURCE].[SPONSORIDFIELD] end,
@DONORTORECIPIENTCONVERSIONRECIPIENTIDFIELDNAME = case when @ISBBEC = 1 then 'ID' else [MKTSPONSORSHIPRECORDSOURCE].[SPONSORSHIPSYSTEMIDFIELD] end
from dbo.[MKTSPONSORSHIPRECORDSOURCE]
inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSPONSORSHIPRECORDSOURCE].[QUERYVIEWCATALOGID]
where [MKTSPONSORSHIPRECORDSOURCE].[ID] = @RECORDSOURCEID;
end
/* Get the excluded DonorIDs and their segment names... */
--Put the segment view IDs into a temp table first to improve performance of the "except" statement on very large data...
set @SQL = 'create table #TEMP ([ID] ' + @PKDATATYPE + ' not null primary key);' + char(13) +
char(13) +
'insert into #TEMP ([ID])' + char(13);
if @SEGMENTTYPECODE = 1 and @USECONSTITUENTFILEIMPORTTABLE = 1
set @SQL = @SQL + ' select distinct [RECORDID] from dbo.[' + @CONSTITUENTFILEIMPORTTABLE + '] where [SEGMENTATIONSEGMENTID] = @SEGMENTATIONSEGMENTID;' + char(13);
else
set @SQL = @SQL + ' select [ID] from dbo.' + @SEGMENTVIEWNAME + ';' + char(13);
set @SQL = @SQL + char(13) +
'insert into dbo.[' + @EXCLUDEDIDSTEMPTABLENAME + '] ([DONORID], [SEGMENTID], [SEGMENTNAME], [QUERYVIEWCATALOGID])' + char(13) +
' select ' + case when @NEEDDISTINCT = 1 then 'distinct' else '' end + char(13) +
' ' + (case when @EFFORTTYPECODE = 0 then '[SEGMENTVIEW].[ID]' else '[' + @DONORTORECIPIENTCONVERSIONTABLENAME + '].[' + @DONORTORECIPIENTCONVERSIONDONORIDFIELDNAME + ']' end) + ',' + char(13) +
' @SEGMENTID,' + char(13) +
' @SEGMENTNAME,' + char(13) +
' @QUERYVIEWCATALOGID' + char(13) +
' from #TEMP as [SEGMENTVIEW]' + char(13);
if @EFFORTTYPECODE <> 0
set @SQL += ' inner join dbo.[' + @DONORTORECIPIENTCONVERSIONTABLENAME + '] on [' + @DONORTORECIPIENTCONVERSIONTABLENAME + '].[' + @DONORTORECIPIENTCONVERSIONRECIPIENTIDFIELDNAME + '] = [SEGMENTVIEW].[ID]' + char(13);
if @EFFORTTYPECODE = 2 --membership
set @SQL += ' and [' + @DONORTORECIPIENTCONVERSIONTABLENAME + '].[ISPRIMARY] = 1 and [' + @DONORTORECIPIENTCONVERSIONTABLENAME + '].[ISDROPPED] = 0' + char(13) +
' inner join dbo.[MEMBERSHIP] on [MEMBERSHIP].[ID] = [' + @DONORTORECIPIENTCONVERSIONTABLENAME + '].[' + @DONORTORECIPIENTCONVERSIONRECIPIENTIDFIELDNAME + '] and [MEMBERSHIP].[STATUSCODE] <> 1' + char(13);
set @SQL += ' except' + char(13) +
' select ' + case when @NEEDDISTINCT = 1 then 'distinct' else '' end + char(13) +
' [DATATABLE].[DONORID],'+ char(13) +
' @SEGMENTID,' + char(13) +
' @SEGMENTNAME,' + char(13) +
' @QUERYVIEWCATALOGID' + char(13) +
' from dbo.[' + @DATATABLE + '] as [DATATABLE]' + char(13) +
' where [DATATABLE].[SEGMENTID] = @SEGMENTATIONSEGMENTID;' + char(13) +
char(13) +
'drop table #TEMP;';
--print @SQL + char(13);
exec sp_executesql @SQL, N'@SEGMENTID uniqueidentifier, @SEGMENTNAME nvarchar(100), @QUERYVIEWCATALOGID uniqueidentifier, @SEGMENTATIONSEGMENTID uniqueidentifier', @SEGMENTID = @SEGMENTID, @SEGMENTNAME = @SEGMENTNAME, @QUERYVIEWCATALOGID = @QUERYVIEWCATALOGID, @SEGMENTATIONSEGMENTID = @SEGMENTATIONSEGMENTID;
fetch next from SEGMENTCURSOR into @SEGMENTATIONSEGMENTID, @SEGMENTID, @SEGMENTNAME, @SEGMENTTYPECODE, @SEGMENTVIEWNAME, @RECORDSOURCEID, @RECORDSOURCETYPE, @ISBBEC;
end
close SEGMENTCURSOR;
deallocate SEGMENTCURSOR;
end
--Add an index to DONORID...
set @SQL = 'create nonclustered index [IX_' + replace(@EXCLUDEDIDSTEMPTABLENAME, '#', '') + '_DONORID] on dbo.[' + @EXCLUDEDIDSTEMPTABLENAME + '] ([DONORID])';
exec (@SQL);
--Add an index to SEGMENTID...
set @SQL = 'create nonclustered index [IX_' + replace(@EXCLUDEDIDSTEMPTABLENAME, '#', '') + '_SEGMENTID] on dbo.[' + @EXCLUDEDIDSTEMPTABLENAME + '] ([SEGMENTID])';
exec (@SQL);
--Add an index to QUERYVIEWCATALOGID...
set @SQL = 'create nonclustered index [IX_' + replace(@EXCLUDEDIDSTEMPTABLENAME, '#', '') + '_QUERYVIEWCATALOGID] on dbo.[' + @EXCLUDEDIDSTEMPTABLENAME + '] ([QUERYVIEWCATALOGID]) include ([DONORID])';
exec (@SQL);
--Update the donor name for each parent queryview....
declare @PARENTCURSOR cursor;
set @SQL = 'declare @QUERYVIEWS table (' + char(13) +
' [SEGMENTID] uniqueidentifier not null,' + char(13) +
' [QUERYVIEWCATALOGID] uniqueidentifier not null' + char(13) +
');' + char(13) +
char(13) +
'insert into @QUERYVIEWS ([SEGMENTID], [QUERYVIEWCATALOGID])' + char(13) +
' select distinct' + char(13) +
' [SEGMENTID],' + char(13) +
' [QUERYVIEWCATALOGID]' + char(13) +
' from dbo.[' + @EXCLUDEDIDSTEMPTABLENAME + '];' + char(13) +
char(13) +
'set @PARENTCURSOR = cursor local fast_forward for' + char(13) +
' select distinct' + char(13) +
' [MKTSEGMENT].[QUERYVIEWCATALOGID],' + char(13) +
' [MKTRECORDSOURCEFIELDMAPPINGS].[FULLNAMEFIELD],' + char(13) +
' [MKTRECORDSOURCEFIELDMAPPINGS].[FIRSTNAMEFIELD],' + char(13) +
' [MKTRECORDSOURCEFIELDMAPPINGS].[MIDDLENAMEFIELD],' + char(13) +
' [MKTRECORDSOURCEFIELDMAPPINGS].[LASTNAMEFIELD],' + char(13) +
' [MKTRECORDSOURCEFIELDMAPPINGS].[ORGNAMEFIELD],' + char(13) +
' [QUERYVIEWCATALOG].[ID],' + char(13) +
' [QUERYVIEWCATALOG].[OBJECTNAME],' + char(13) +
' [QUERYVIEWCATALOG].[PRIMARYKEYFIELD]' + char(13) +
' from @QUERYVIEWS as [QV]' + char(13) +
' inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [QV].[SEGMENTID]' + char(13) +
' inner join dbo.[MKTRECORDSOURCEFIELDMAPPINGS] on [MKTRECORDSOURCEFIELDMAPPINGS].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]' + char(13) +
' inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [QV].[QUERYVIEWCATALOGID];' + char(13) +
'open @PARENTCURSOR;';
exec sp_executesql @SQL, N'@PARENTCURSOR cursor output', @PARENTCURSOR = @PARENTCURSOR output;
fetch next from @PARENTCURSOR into @RECORDSOURCEID, @FULLNAMEFIELDNAME, @FIRSTNAMEFIELDNAME, @MIDDLENAMEFIELDNAME, @LASTNAMEFIELDNAME, @ORGNAMEFIELDNAME, @QUERYVIEWCATALOGID, @PARENTTABLENAME, @PARENTID;
while (@@FETCH_STATUS = 0)
begin
--See if the fields exist in the table/view and clear the name if necessary...
if @FULLNAMEFIELDNAME <> '' and exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @FULLNAMEFIELDNAME)
set @FULLNAMEFIELDNAME = '[PARENT].[' + @FULLNAMEFIELDNAME + ']';
else
set @FULLNAMEFIELDNAME = '';
--Lists don't necessarily have the fullname field mapped, so we may have to build the name...
if @RECORDSOURCEID <> @QUERYVIEWCATALOGID or @FULLNAMEFIELDNAME = ''
begin
if @FIRSTNAMEFIELDNAME <> '' and exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @FIRSTNAMEFIELDNAME)
set @FIRSTNAMEFIELDNAME = '[PARENT].[' + @FIRSTNAMEFIELDNAME + ']';
else
set @FIRSTNAMEFIELDNAME = '';
if @MIDDLENAMEFIELDNAME <> '' and exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @MIDDLENAMEFIELDNAME)
set @MIDDLENAMEFIELDNAME = '[PARENT].[' + @MIDDLENAMEFIELDNAME + ']';
else
set @MIDDLENAMEFIELDNAME = '';
if @LASTNAMEFIELDNAME <> '' and exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @LASTNAMEFIELDNAME)
set @LASTNAMEFIELDNAME = '[PARENT].[' + @LASTNAMEFIELDNAME + ']';
else
set @LASTNAMEFIELDNAME = '';
if @ORGNAMEFIELDNAME <> '' and exists(select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @ORGNAMEFIELDNAME)
set @ORGNAMEFIELDNAME = '[PARENT].[' + @ORGNAMEFIELDNAME + ']';
else
set @ORGNAMEFIELDNAME = '';
set @FULLNAMEFIELDNAME = 'coalesce(' +
(case when @ORGNAMEFIELDNAME = '' then '' else '(case isnull(' + @ORGNAMEFIELDNAME + ', '''') when '''' then null else ' + @ORGNAMEFIELDNAME + ' end), ' end) +
(case when @FULLNAMEFIELDNAME = '' then '' else '(case isnull(' + @FULLNAMEFIELDNAME + ', '''') when '''' then null else ' + @FULLNAMEFIELDNAME + ' end), ' end) +
(case when @FIRSTNAMEFIELDNAME = '' then '' else '(case isnull(' + @FIRSTNAMEFIELDNAME + ', '''') when '''' then '''' else ' + @FIRSTNAMEFIELDNAME + ' + '' '' end) + ' end) +
(case when @MIDDLENAMEFIELDNAME = '' then '' else '(case isnull(' + @MIDDLENAMEFIELDNAME + ', '''') when '''' then '''' else left(' + @MIDDLENAMEFIELDNAME + ', 1) + ''. '' end) + ' end) +
(case when @LASTNAMEFIELDNAME = '' then '' else 'isnull(' + @LASTNAMEFIELDNAME + ', ''''), ' end) +
'''''' +
')';
end
--Update the donor name for each parent queryview....
set @SQL = 'update dbo.[' + @EXCLUDEDIDSTEMPTABLENAME + '] set' + char(13) +
' [NAME] = ' + @FULLNAMEFIELDNAME + char(13) +
'from dbo.[' + @EXCLUDEDIDSTEMPTABLENAME + '] as [EXCLUDEDIDS]' + char(13) +
'inner join dbo.[' + @PARENTTABLENAME + '] as [PARENT] on [PARENT].[' + @PARENTID + '] = [EXCLUDEDIDS].[DONORID]' + char(13) +
'where [EXCLUDEDIDS].[QUERYVIEWCATALOGID] = @QUERYVIEWCATALOGID';
--print @SQL + char(13);
exec sp_executesql @SQL, N'@QUERYVIEWCATALOGID uniqueidentifier', @QUERYVIEWCATALOGID = @QUERYVIEWCATALOGID;
fetch next from @PARENTCURSOR into @RECORDSOURCEID, @FULLNAMEFIELDNAME, @FIRSTNAMEFIELDNAME, @MIDDLENAMEFIELDNAME, @LASTNAMEFIELDNAME, @ORGNAMEFIELDNAME, @QUERYVIEWCATALOGID, @PARENTTABLENAME, @PARENTID;
end
close @PARENTCURSOR;
deallocate @PARENTCURSOR;
--Recreate the index on DONORID to include NAME and SEGMENTNAME. It is much faster to recreate this index after updating the NAME column above...
set @SQL = 'create nonclustered index [IX_' + replace(@EXCLUDEDIDSTEMPTABLENAME, '#', '') + '_DONORID] on dbo.[' + @EXCLUDEDIDSTEMPTABLENAME + '] ([DONORID]) include ([NAME], [SEGMENTNAME]) with (DROP_EXISTING = ON)';
exec (@SQL);
end try
begin catch
exec dbo.[USP_RAISE_ERROR];
return 1;
end catch
return 0;