USP_ATTRIBUTECATEGORY_GENERATECONSTITUENTMERGEPROC
Generates the store procedure used to merge constituent attributes.
Definition
Copy
CREATE procedure dbo.USP_ATTRIBUTECATEGORY_GENERATECONSTITUENTMERGEPROC
as
-- This procedure exists to avoid the need for executing dynamic SQL in a cursor
-- for each merge pair during a constituent merge. It builds one long stored procedure
-- that is regenerated on attribute category add/edit/delete. That stored procedure is then
-- all the code that needs to be run during the merge task.
set nocount on;
-- Define common sproc header and footer
declare @SPROCHEADER nvarchar(max) = '
%CREATEORALTER% procedure [dbo].[%SPNAME%]
(
@SOURCEID uniqueidentifier,
@TARGETID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier
)
as
set nocount on;
/*** THIS PROCEDURE IS AUTO-GENERATED by dbo.USP_ATTRIBUTECATEGORY_GENERATECONSTITUENTMERGEPROC ***/
/*** Please make changes in that procedure. Otherwise, they may be overwritten. ***/
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
';
declare @SPROCFOOTER nvarchar(15) = '
return 0;';
declare @CONSTITUENTCREATEORALTER nvarchar(10) = case when exists(select * from sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_ATTRIBUTECATEGORY_CONSTITUENTMERGE_AUTOGEN]') AND type in (N'P', N'PC'))
then 'alter'
else 'create'
end;
declare @CONSTITUENTSPROC nvarchar(max) = replace(replace(@SPROCHEADER, '%CREATEORALTER%', @CONSTITUENTCREATEORALTER), '%SPNAME%', 'USP_ATTRIBUTECATEGORY_CONSTITUENTMERGE_AUTOGEN');
declare @MODELINGANDPROPENSITYCREATEORALTER nvarchar(10) = case when exists(select * from sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_ATTRIBUTECATEGORY_MODELINGANDPROPENSITY_CONSTITUENTMERGE_AUTOGEN]') AND type in (N'P', N'PC'))
then 'alter'
else 'create'
end;
declare @MODELINGANDPROPENSITYSPROC nvarchar(max) = replace(replace(@SPROCHEADER, '%CREATEORALTER%', @MODELINGANDPROPENSITYCREATEORALTER), '%SPNAME%', 'USP_ATTRIBUTECATEGORY_MODELINGANDPROPENSITY_CONSTITUENTMERGE_AUTOGEN');
if dbo.UFN_ATTRIBUTECATEGORY_DEFINEDFORRECORDTYPE('Constituent') = 1 or dbo.UFN_ATTRIBUTECATEGORY_DEFINEDFORRECORDTYPE('Model Scores and Ratings') = 1
begin
declare @TABLENAME nvarchar(50);
declare @CATEGORYID uniqueidentifier;
declare @ONEPERECORD bit;
declare @VALUEFIELD nvarchar(50);
declare @ATTRIBUTERECORDTYPEID uniqueidentifier;
declare @SQL nvarchar(max);
declare @CONSTITUENTRECORDTYPEID uniqueidentifier;
select @CONSTITUENTRECORDTYPEID = ID from dbo.RECORDTYPE where NAME = 'Constituent';
declare @MODELINGANDPROPENSITYRECORDTYPEID uniqueidentifier;
select @MODELINGANDPROPENSITYRECORDTYPEID = ID from dbo.RECORDTYPE where BASETABLENAME = N'MODELINGANDPROPENSITY';
declare ATTRTABLECURSOR cursor local fast_forward for
select
TABLECATALOG.TABLENAME,
ATTRIBUTECATEGORY.ID,
ATTRIBUTECATEGORY.ONLYALLOWONEPERRECORD,
case DATATYPECODE
when 0 then 'VALUE' --Text
when 1 then 'VALUE' --Number
when 2 then 'VALUE' --Date
when 3 then 'VALUE' --Currency
when 4 then 'BOOLEANCODE' -- Yes/No
when 5 then CODETABLECATALOG.DBTABLENAME + 'ID' --Code Table
when 6 then 'CONSTITUENTVALUEID' --Constituent
when 7 then 'VALUE' --Fuzzy Date
when 8 then 'VALUE' -- Time
when 9 then 'VALUE' -- Memo
end VALUEFIELD,
ATTRIBUTERECORDTYPE.RECORDTYPEID
from ATTRIBUTECATEGORY
inner join TABLECATALOG on TABLECATALOG.ID = ATTRIBUTECATEGORY.TABLECATALOGID
inner join ATTRIBUTERECORDTYPE on ATTRIBUTERECORDTYPE.ID = ATTRIBUTECATEGORY.ATTRIBUTERECORDTYPEID
left outer join CODETABLECATALOG on CODETABLECATALOG.ID = ATTRIBUTECATEGORY.CODETABLECATALOGID
where ATTRIBUTERECORDTYPE.RECORDTYPEID in (@CONSTITUENTRECORDTYPEID, @MODELINGANDPROPENSITYRECORDTYPEID);
open ATTRTABLECURSOR;
fetch next from ATTRTABLECURSOR into @TABLENAME, @CATEGORYID, @ONEPERECORD, @VALUEFIELD, @ATTRIBUTERECORDTYPEID;
while @@fetch_status = 0
begin
declare @CONSTITUENTIDCOLUMN nvarchar(25);
if @ATTRIBUTERECORDTYPEID = @CONSTITUENTRECORDTYPEID
set @CONSTITUENTIDCOLUMN = 'CONSTITUENTID'
else
set @CONSTITUENTIDCOLUMN = 'MODELINGANDPROPENSITYID'
set @SQL = '
/*** Attribute Category ID = ' + CONVERT(nvarchar(40), @CATEGORYID) + ' ***/';
if @ONEPERECORD = 1
begin
-- Determine if the table has start and end dates. This fields weren't added to attributes in all versions
-- so some attribute tables may not have them as columns.
declare @ADDITIONALCOLUMNSTOCOPY nvarchar(30) = ''
-- STARTDATE and ENDDATE were added at the same time so if one exists, they both do
if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TABLENAME and COLUMN_NAME = 'STARTDATE')
set @ADDITIONALCOLUMNSTOCOPY = ' ,STARTDATE,ENDDATE'
set @SQL = @SQL + '
if not exists
(
select top(1) ID
from dbo.' + @TABLENAME + '
where ID = @TARGETID
)
begin
insert into dbo.' + @TABLENAME + '
(ID, ' + @VALUEFIELD + ', COMMENT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED' + @ADDITIONALCOLUMNSTOCOPY + ')
select
@TARGETID,
' + @VALUEFIELD +',
COMMENT,
ADDEDBYID,
@CHANGEAGENTID,
DATEADDED,
@CURRENTDATE
' + @ADDITIONALCOLUMNSTOCOPY + '
from
dbo.' + @TABLENAME + '
where
ID = @SOURCEID;
exec dbo.USP_' + @TABLENAME + '_DELETEBYID_WITHCHANGEAGENTID @SOURCEID, @CHANGEAGENTID;
end'
end
else
set @SQL = @SQL + '
update dbo.' + @TABLENAME + '
set ' + @CONSTITUENTIDCOLUMN + ' = @TARGETID, CHANGEDBYID = @CHANGEAGENTID
where ' + @CONSTITUENTIDCOLUMN + ' = @SOURCEID;'
if @ATTRIBUTERECORDTYPEID = @CONSTITUENTRECORDTYPEID
set @CONSTITUENTSPROC = @CONSTITUENTSPROC + @SQL + nchar(13) + nchar(13);
else
set @MODELINGANDPROPENSITYSPROC = @MODELINGANDPROPENSITYSPROC + @SQL + nchar(13) + nchar(13);
fetch next from ATTRTABLECURSOR into @TABLENAME, @CATEGORYID, @ONEPERECORD, @VALUEFIELD, @ATTRIBUTERECORDTYPEID;
end
close ATTRTABLECURSOR;
deallocate ATTRTABLECURSOR;
end
set @CONSTITUENTSPROC = @CONSTITUENTSPROC + @SPROCFOOTER;
exec(@CONSTITUENTSPROC);
set @MODELINGANDPROPENSITYSPROC = @MODELINGANDPROPENSITYSPROC + @SPROCFOOTER;
exec(@MODELINGANDPROPENSITYSPROC);
return 0;