USP_MKTSOURCEANALYSISRULE_CREATEDATATABLE
Creates the source analysis rule data table for the specified record source.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @RECORDSOURCEID | uniqueidentifier | IN | |
| @ISEXCLUSION | bit | IN |
Definition
Copy
CREATE procedure dbo.[USP_MKTSOURCEANALYSISRULE_CREATEDATATABLE]
(
@RECORDSOURCEID uniqueidentifier,
@ISEXCLUSION bit = 0
)
with execute as owner
as
set nocount on;
declare @SQL nvarchar(max);
declare @DATATABLE nvarchar(128);
declare @DONORIDDATATYPE nvarchar(128);
declare @DATATYPE nvarchar(255);
declare @MAXLENGTH int;
declare @INDEXSQL nvarchar(max);
declare @USECOMPRESSION bit = (case when serverproperty('engineedition') = 3 then 1 else 0 end);
begin try
if @ISEXCLUSION = 0
set @DATATABLE = dbo.[UFN_MKTSOURCEANALYSISRULE_MAKETABLENAME](@RECORDSOURCEID);
else
set @DATATABLE = dbo.[UFN_MKTSOURCEANALYSISRULEEXCLUSION_MAKETABLENAME](@RECORDSOURCEID);
set @DONORIDDATATYPE = dbo.[UFN_MKTRECORDSOURCE_GETDONORIDDATATYPE](@RECORDSOURCEID);
if not exists(select 1 from sys.tables where [name] = @DATATABLE)
begin --make the table as it is new
/* Create the table */
set @SQL = 'create table [dbo].[' + @DATATABLE + '] (' + char(13) +
' [ID] bigint identity(1,1) not null constraint [PK_' + @DATATABLE + '] primary key clustered ([ID] asc) with (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100),' + char(13);
if @ISEXCLUSION = 1
set @SQL = @SQL + ' [SEGMENTATIONEXCLUSIONID] [uniqueidentifier] null,' + char(13);
set @SQL = @SQL +
' [MAILINGID] [uniqueidentifier] not null,' + char(13) +
' [SEGMENTID] [uniqueidentifier] not null,' + char(13) +
' [TESTSEGMENTID] [uniqueidentifier] null,' + char(13) +
' [DONORID] ' + @DONORIDDATATYPE + ' not null,' + char(13) +
' [FINDERNUMBER] bigint null' + char(13);
set @INDEXSQL = 'create nonclustered index [IX_' + @DATATABLE + '_MAILINGID_DONORID] on dbo.[' + @DATATABLE + '] ([MAILINGID] asc, [DONORID] asc) include ([SEGMENTID], [TESTSEGMENTID]) with (PAD_INDEX = off, SORT_IN_TEMPDB = off, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 80' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP];' + char(13) +
'create nonclustered index [IX_' + @DATATABLE + '_SEGMENTID] on dbo.[' + @DATATABLE + '] ([SEGMENTID] asc) with (PAD_INDEX = off, SORT_IN_TEMPDB = off, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 80' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP];' + char(13) +
'create nonclustered index [IX_' + @DATATABLE + '_TESTSEGMENTID] on dbo.[' + @DATATABLE + '] ([TESTSEGMENTID] asc) with (PAD_INDEX = off, SORT_IN_TEMPDB = off, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 80' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP];' + char(13) +
'create nonclustered index [IX_' + @DATATABLE + '_DONORID] on dbo.[' + @DATATABLE + '] ([DONORID] asc) include ([MAILINGID]) with (PAD_INDEX = off, SORT_IN_TEMPDB = off, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 80' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP];' + char(13) +
'create nonclustered index [IX_' + @DATATABLE + '_FINDERNUMBER] on dbo.[' + @DATATABLE + '] ([FINDERNUMBER] asc) with (PAD_INDEX = off, SORT_IN_TEMPDB = off, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 90' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP];' + char(13);
if @ISEXCLUSION = 1
set @INDEXSQL += 'create nonclustered index [IX_' + @DATATABLE + '_SEGMENTATIONEXCLUSIONID] on dbo.[' + @DATATABLE + '] ([SEGMENTATIONEXCLUSIONID] asc) with (PAD_INDEX = off, SORT_IN_TEMPDB = off, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 80' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP];' + char(13);
/* Add fields from the rule */
declare @QUERYFIELD nvarchar(255);
declare @DBOBJECTNAME nvarchar(255);
declare @ADHOCQUERYID uniqueidentifier;
declare @SMARTFIELDID uniqueidentifier;
declare @DISPLAYNAME nvarchar(255);
declare @CACHETABLECOLUMNNAME nvarchar(255);
declare @CACHETABLECOLUMNLENGTH integer;
declare field_cursor cursor local fast_forward for
select
[MKTSOURCEANALYSISRULEFIELDS].[QUERYFIELD],
[MKTSOURCEANALYSISRULEFIELDS].[DBOBJECTNAME],
[MKTSOURCEANALYSISRULEFIELDS].[ADHOCQUERYID],
[MKTSOURCEANALYSISRULEFIELDS].[SMARTFIELDID],
[MKTSOURCEANALYSISRULEFIELDS].[NAME],
[MKTSOURCEANALYSISRULEFIELDS].[CACHETABLECOLUMNNAME]
from dbo.[MKTSOURCEANALYSISRULEFIELDS]
inner join dbo.[MKTSOURCEANALYSISRULES] on [MKTSOURCEANALYSISRULEFIELDS].[SOURCEANALYSISRULEID] = [MKTSOURCEANALYSISRULES].[ID]
where [MKTSOURCEANALYSISRULES].[RECORDSOURCEID] = @RECORDSOURCEID;
open field_cursor;
fetch next from field_cursor into @QUERYFIELD, @DBOBJECTNAME, @ADHOCQUERYID, @SMARTFIELDID, @DISPLAYNAME, @CACHETABLECOLUMNNAME;
while @@FETCH_STATUS = 0
begin
if (@ADHOCQUERYID is not null) or (isnull(patindex('V_MKTSEGMENT_%', @DBOBJECTNAME), 0) > 0)
begin
set @SQL = @SQL + ',[' + @CACHETABLECOLUMNNAME + '] [bit] null' + char(13);
if @ISEXCLUSION = 0
set @INDEXSQL = @INDEXSQL + 'create nonclustered index [IX_' + @DATATABLE + '_' + replace(newid(), '-', '') + '] on [dbo].[' + @DATATABLE + '] ( [' + @CACHETABLECOLUMNNAME + '] asc ) with (PAD_INDEX = off, SORT_IN_TEMPDB = off, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 80' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP];' + char(13);
end
else
begin
select
@DATATYPE = t1.[name],
@MAXLENGTH = c1.[max_length]
from sys.types as t1
inner join sys.columns as c1 on t1.[user_type_id] = c1.[user_type_id]
where c1.[object_id] = object_id(@DBOBJECTNAME)
and c1.[name] = @QUERYFIELD;
set @SQL = @SQL + ',[' + @CACHETABLECOLUMNNAME + '] [' + @DATATYPE + ']';
if @ISEXCLUSION = 0
set @INDEXSQL = @INDEXSQL + 'create nonclustered index [IX_' + @DATATABLE + '_' + replace(newid(), '-', '') + '] on [dbo].[' + @DATATABLE + '] ( [' + @CACHETABLECOLUMNNAME + '] asc ) with (PAD_INDEX = off, SORT_IN_TEMPDB = off, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 80' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP];' + char(13);
if @DATATYPE = 'nvarchar' and @MAXLENGTH > 0
set @MAXLENGTH = @MAXLENGTH/2;
if (@DATATYPE = 'nvarchar' or @DATATYPE = 'varchar') and @MAXLENGTH > 0
set @SQL = @SQL + '(' + cast(@MAXLENGTH as nvarchar) + ') null' + char(13);
else
begin
if (@DATATYPE = 'nvarchar' or @DATATYPE = 'varchar') and @MAXLENGTH = -1
set @SQL = @SQL + '(max) null' + char(13);
else
set @SQL = @SQL + ' null' + char(13);
end
end
fetch next from field_cursor into @QUERYFIELD, @DBOBJECTNAME, @ADHOCQUERYID, @SMARTFIELDID, @DISPLAYNAME, @CACHETABLECOLUMNNAME;
end
close field_cursor;
deallocate field_cursor;
set @SQL += ',constraint [CK_' + @DATATABLE + '_FINDERNUMBER] CHECK ([FINDERNUMBER] is null or [FINDERNUMBER] > 0)' + char(13) +
') on [DEFGROUP]';
if @USECOMPRESSION = 1
set @SQL = @SQL + ' WITH (DATA_COMPRESSION = PAGE)';
exec (@SQL);
exec (@INDEXSQL);
/* Grant select, insert, update, delete and alter rights on the new table */
set @SQL = 'grant select, insert, update, alter, delete on dbo.[' + @DATATABLE + '] to BBAPPFXSERVICEROLE';
exec (@SQL);
/* Add the foreign key to the mailing table */
set @SQL = 'alter table [dbo].[' + @DATATABLE + '] with check add constraint [FK_' + @DATATABLE + '_MAILINGID] foreign key([MAILINGID])' + char(13) +
' references [dbo].[MKTSEGMENTATION] ([ID])' + char(13) +
' on delete no action';
exec (@SQL);
/* Add the foreign key to the segment table */
set @SQL = 'alter table [dbo].[' + @DATATABLE + '] with check add constraint [FK_' + @DATATABLE + '_SEGMENTID] foreign key([SEGMENTID])' + char(13) +
' references [dbo].[MKTSEGMENTATIONSEGMENT] ([ID])' + char(13) +
' on delete no action';
exec (@SQL);
/* Add the foreign key to the test segment table */
set @SQL = 'alter table [dbo].[' + @DATATABLE + '] with check add constraint [FK_' + @DATATABLE + '_TESTSEGMENTID] foreign key([TESTSEGMENTID])' + char(13) +
' references [dbo].[MKTSEGMENTATIONTESTSEGMENT] ([ID])' + char(13) +
' on delete no action';
exec (@SQL);
if @ISEXCLUSION = 1
begin
/* Add the foreign key to the segmentation exclusion table */
set @SQL = 'alter table [dbo].[' + @DATATABLE + '] with check add constraint [FK_' + @DATATABLE + '_SEGMENTATIONEXLCUSIONID] foreign key([SEGMENTATIONEXCLUSIONID])' + char(13) +
' references [dbo].[MKTSEGMENTATIONEXCLUSION] ([ID])' + char(13) +
' on delete cascade';
exec (@SQL);
end
end
else -- see if any fields were added or changed
begin
--Don't add indexes to new SAR fields here because it may take a long time on large databases. When adding new fields
--to the SAR, there is now a business process that gets called to add the indexes to any new SAR fields.
declare field_cursor cursor local fast_forward for
select
[MKTSOURCEANALYSISRULEFIELDS].[QUERYFIELD],
[MKTSOURCEANALYSISRULEFIELDS].[DBOBJECTNAME],
[MKTSOURCEANALYSISRULEFIELDS].[ADHOCQUERYID],
[MKTSOURCEANALYSISRULEFIELDS].[SMARTFIELDID],
[MKTSOURCEANALYSISRULEFIELDS].[NAME],
[MKTSOURCEANALYSISRULEFIELDS].[CACHETABLECOLUMNNAME]
from dbo.[MKTSOURCEANALYSISRULEFIELDS]
inner join dbo.[MKTSOURCEANALYSISRULES] on [MKTSOURCEANALYSISRULEFIELDS].[SOURCEANALYSISRULEID] = [MKTSOURCEANALYSISRULES].[ID]
where [MKTSOURCEANALYSISRULES].[RECORDSOURCEID] = @RECORDSOURCEID;
open field_cursor;
fetch next from field_cursor into @QUERYFIELD, @DBOBJECTNAME, @ADHOCQUERYID, @SMARTFIELDID, @DISPLAYNAME, @CACHETABLECOLUMNNAME;
while @@FETCH_STATUS = 0
begin
set @SQL = '';
if exists(select 1 from sys.columns as c1 where (c1.[object_id] = object_id(@DATATABLE) and c1.name = @CACHETABLECOLUMNNAME))
begin
-- WI 271034: smart field value group names can be increased in length, and so if a source analysis rule is based on one of these,
-- the column length in the cache table has to be increased accordingly
if (isnull(patindex('V_QUERY_SMARTFIELD%', @DBOBJECTNAME), 0) > 0) and @QUERYFIELD = 'VALUEGROUP'
begin
select
@DATATYPE = t1.[name],
@MAXLENGTH = c1.[max_length]
from sys.types as t1
inner join sys.columns as c1 on t1.[user_type_id] = c1.[user_type_id]
where c1.[object_id] = object_id(@DBOBJECTNAME)
and c1.[name] = @QUERYFIELD;
if @DATATYPE = 'varchar' or @DATATYPE = 'nvarchar'
begin
select
@CACHETABLECOLUMNLENGTH = c1.[max_length]
from sys.types as t1
inner join sys.columns as c1 on t1.[user_type_id] = c1.[user_type_id]
where c1.[object_id] = object_id(@DATATABLE)
and c1.[name] = @CACHETABLECOLUMNNAME;
if @MAXLENGTH > @CACHETABLECOLUMNLENGTH or (@MAXLENGTH = -1 and @CACHETABLECOLUMNLENGTH > 0)
begin
if @DATATYPE = 'nvarchar' and @MAXLENGTH > 0
set @MAXLENGTH = @MAXLENGTH/2;
set @SQL = 'alter table [' + @DATATABLE + '] alter column [' + @CACHETABLECOLUMNNAME + '] [' + @DATATYPE + ']';
if @MAXLENGTH = -1
set @SQL = @SQL + '(max) null';
else
set @SQL = @SQL + '(' + cast(@MAXLENGTH as nvarchar) + ') null';
exec (@SQL);
end
end
end
end
else
begin
if (@ADHOCQUERYID is not null) or (isnull(patindex('V_MKTSEGMENT_%', @DBOBJECTNAME), 0) > 0) or (isnull(patindex('UFN_%', @DBOBJECTNAME), 0) > 0)
set @SQL = 'alter table [' + @DATATABLE + '] add [' + @CACHETABLECOLUMNNAME + '] [bit] null';
else
begin
select
@DATATYPE = t1.[name],
@MAXLENGTH = c1.[max_length]
from sys.types as t1
inner join sys.columns as c1 on t1.[user_type_id] = c1.[user_type_id]
where c1.[object_id] = object_id(@DBOBJECTNAME)
and c1.[name] = @QUERYFIELD;
set @SQL = 'alter table [' + @DATATABLE + '] add [' + @CACHETABLECOLUMNNAME + '] [' + @DATATYPE + ']';
if @DATATYPE = 'nvarchar' and @MAXLENGTH > 0
set @MAXLENGTH = @MAXLENGTH/2;
if (@DATATYPE = 'nvarchar' or @DATATYPE = 'varchar') and @MAXLENGTH > 0
set @SQL = @SQL + '(' + cast(@MAXLENGTH as nvarchar) + ') null';
else
begin
if (@DATATYPE = 'nvarchar' or @DATATYPE = 'varchar') and @MAXLENGTH = -1
set @SQL = @SQL + '(max) null' + char(13);
else
set @SQL = @SQL + ' null';
end
end
exec (@SQL);
end
fetch next from field_cursor into @QUERYFIELD, @DBOBJECTNAME, @ADHOCQUERYID, @SMARTFIELDID, @DISPLAYNAME, @CACHETABLECOLUMNNAME;
end
close field_cursor;
deallocate field_cursor;
set @SQL = 'grant select, insert, update, alter, delete on dbo.[' + @DATATABLE + '] to BBAPPFXSERVICEROLE';
exec (@SQL);
end
end try
begin catch
exec dbo.[USP_RAISE_ERROR];
return 1;
end catch
return 0;