USP_DATALIST_MKTSEGMENT_MEMBER
Displays a list of all members from a given segment.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SEGMENTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @TOTALROWS | int | IN | Maximum rows to return |
| @FILTER | nvarchar(253) | IN | Find |
Definition
Copy
CREATE procedure dbo.[USP_DATALIST_MKTSEGMENT_MEMBER]
(
@SEGMENTID uniqueidentifier,
@TOTALROWS int = 500,
@FILTER nvarchar(253) = null
)
as
set nocount on;
declare @PARENTTABLENAME nvarchar(255);
declare @PARENTID nvarchar(255);
declare @SEGMENTVIEWNAME nvarchar(255);
declare @RECORDSOURCEID uniqueidentifier;
declare @RECORDSOURCETYPE as tinyint;
declare @ISBBEC bit = (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end);
begin try
select
@RECORDSOURCETYPE = dbo.[UFN_MKTSEGMENT_GETMARKETINGRECORDTYPE]([ID]),
@RECORDSOURCEID = [QUERYVIEWCATALOGID]
from dbo.[MKTSEGMENT]
where [ID] = @SEGMENTID;
if @RECORDSOURCETYPE = 1 --Record Source
select
@PARENTTABLENAME = [QUERYVIEWCATALOG].[OBJECTNAME],
@PARENTID = [QUERYVIEWCATALOG].[PRIMARYKEYFIELD],
@SEGMENTVIEWNAME = dbo.[UFN_MKTSEGMENT_MAKEVIEWNAME]([MKTSEGMENT].[ID])
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
@PARENTTABLENAME = [QUERYVIEWCATALOG].[OBJECTNAME],
@PARENTID = [QUERYVIEWCATALOG].[PRIMARYKEYFIELD],
@SEGMENTVIEWNAME = (case when [MKTSEGMENTLIST].[PARENTSEGMENTID] is null then dbo.[UFN_MKTSEGMENTLIST_MAKEVIEWNAME_FORIDSET]([MKTSEGMENTLIST].[ID], 0) else dbo.[UFN_MKTSEGMENT_MAKEVIEWNAME]([MKTSEGMENT].[ID]) end)
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
@PARENTTABLENAME = [CQV].[OBJECTNAME],
@PARENTID = [CQV].[PRIMARYKEYFIELD],
@SEGMENTVIEWNAME = dbo.[UFN_MKTSEGMENT_MAKEVIEWNAME]([MKTSEGMENT].[ID])
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
-- 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 @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);
declare @LOOKUPIDFIELDNAME 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]
,@CITYFIELDNAME = [CITYFIELD]
,@STATEFIELDNAME = [STATEFIELD]
,@POSTCODEFIELDNAME = [POSTCODEFIELD]
,@CARTFIELDNAME = [CARTFIELD]
,@DPCFIELDNAME = [DPCFIELD]
,@LOTFIELDNAME = [LOTFIELD]
,@PHONENUMBERFIELDNAME = [PHONENUMBERFIELD]
,@EMAILADDRESSFIELDNAME = [EMAILADDRESSFIELD]
,@FIRSTGIFTDATEFIELDNAME = [FIRSTGIFTDATEFIELD]
,@LOOKUPIDFIELDNAME = [LOOKUPIDFIELD]
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] = @PARENTTABLENAME and [COLUMN_NAME] = @FULLNAMEFIELDNAME)
set @FULLNAMEFIELDNAME = '[P].[' + @FULLNAMEFIELDNAME + ']';
else
set @FULLNAMEFIELDNAME = '''''';
if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @FIRSTNAMEFIELDNAME)
set @FIRSTNAMEFIELDNAME = '[P].[' + @FIRSTNAMEFIELDNAME + ']';
else
set @FIRSTNAMEFIELDNAME = '''''';
if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @MIDDLENAMEFIELDNAME)
set @MIDDLENAMEFIELDNAME = '[P].[' + @MIDDLENAMEFIELDNAME + ']';
else
set @MIDDLENAMEFIELDNAME = '''''';
if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @LASTNAMEFIELDNAME)
set @LASTNAMEFIELDNAME = '[P].[' + @LASTNAMEFIELDNAME + ']';
else
set @LASTNAMEFIELDNAME = '''''';
if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @TITLEFIELDNAME)
set @TITLEFIELDNAME = '[P].[' + @TITLEFIELDNAME + ']';
else
set @TITLEFIELDNAME = '''''';
if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @SUFFIXFIELDNAME)
set @SUFFIXFIELDNAME = '[P].[' + @SUFFIXFIELDNAME + ']';
else
set @SUFFIXFIELDNAME = '''''';
if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @COUNTRYFIELDNAME)
set @COUNTRYFIELDNAME = '[P].[' + @COUNTRYFIELDNAME + ']';
else
set @COUNTRYFIELDNAME = '''''';
if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @ADDRESSLINE1FIELDNAME)
set @ADDRESSLINE1FIELDNAME = '[P].[' + @ADDRESSLINE1FIELDNAME + ']';
else
set @ADDRESSLINE1FIELDNAME = '''''';
if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @ADDRESSLINE2FIELDNAME)
set @ADDRESSLINE2FIELDNAME = '[P].[' + @ADDRESSLINE2FIELDNAME + ']';
else
set @ADDRESSLINE2FIELDNAME = '''''';
if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @CITYFIELDNAME)
set @CITYFIELDNAME = '[P].[' + @CITYFIELDNAME + ']';
else
set @CITYFIELDNAME = '''''';
if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @STATEFIELDNAME)
set @STATEFIELDNAME = '[P].[' + @STATEFIELDNAME + ']';
else
if @ISBBEC = 1
begin
if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = 'STATEID_TRANSLATION')
set @STATEFIELDNAME = '[P].[STATEID_TRANSLATION]';
else
set @STATEFIELDNAME = '''''';
end
else
set @STATEFIELDNAME = '''''';
if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @POSTCODEFIELDNAME)
set @POSTCODEFIELDNAME = '[P].[' + @POSTCODEFIELDNAME + ']';
else
set @POSTCODEFIELDNAME = '''''';
if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @CARTFIELDNAME)
set @CARTFIELDNAME = '[P].[' + @CARTFIELDNAME + ']';
else
set @CARTFIELDNAME = '''''';
if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @DPCFIELDNAME)
set @DPCFIELDNAME = '[P].[' + @DPCFIELDNAME + ']';
else
set @DPCFIELDNAME = '''''';
if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @LOTFIELDNAME)
set @LOTFIELDNAME = '[P].[' + @LOTFIELDNAME + ']';
else
set @LOTFIELDNAME = '''''';
if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @PHONENUMBERFIELDNAME)
set @PHONENUMBERFIELDNAME = '[P].[' + @PHONENUMBERFIELDNAME + ']';
else
set @PHONENUMBERFIELDNAME = '''''';
if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @EMAILADDRESSFIELDNAME)
set @EMAILADDRESSFIELDNAME = '[P].[' + @EMAILADDRESSFIELDNAME + ']';
else
set @EMAILADDRESSFIELDNAME = '''''';
if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @FIRSTGIFTDATEFIELDNAME)
set @FIRSTGIFTDATEFIELDNAME = '[P].[' + @FIRSTGIFTDATEFIELDNAME + ']';
else
set @FIRSTGIFTDATEFIELDNAME = '''''';
if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @LOOKUPIDFIELDNAME)
set @LOOKUPIDFIELDNAME = '[P].[' + @LOOKUPIDFIELDNAME + ']';
else
set @LOOKUPIDFIELDNAME = '''''';
--Build the SQL
declare @SQL nvarchar(max);
set @SQL = 'select top ' + cast(@TOTALROWS as nvarchar(10)) + char(13) +
' ' + @FULLNAMEFIELDNAME + ',' + char(13) +
' ' + @FIRSTNAMEFIELDNAME + ',' + char(13) +
' ' + @MIDDLENAMEFIELDNAME + ',' + char(13) +
' ' + @LASTNAMEFIELDNAME + ',' + char(13) +
' ' + @TITLEFIELDNAME + ',' + char(13) +
' ' + @SUFFIXFIELDNAME + ',' + char(13) +
' ' + @COUNTRYFIELDNAME + ',' + char(13) +
' ' + @ADDRESSLINE1FIELDNAME + ',' + char(13) +
' ' + @ADDRESSLINE2FIELDNAME + ',' + char(13) +
' ' + @CITYFIELDNAME + ',' + char(13) +
' ' + @STATEFIELDNAME + ',' + char(13) +
' ' + @POSTCODEFIELDNAME + ',' + char(13) +
' ' + @CARTFIELDNAME + ',' + char(13) +
' ' + @DPCFIELDNAME + ',' + char(13) +
' ' + @LOTFIELDNAME + ',' + char(13) +
' ' + @PHONENUMBERFIELDNAME + ',' + char(13) +
' ' + @EMAILADDRESSFIELDNAME + ',' + char(13) +
' ' + @FIRSTGIFTDATEFIELDNAME + ',' + char(13) +
' ' + @LOOKUPIDFIELDNAME + ',' + char(13) +
' [P].[' + @PARENTID + '],' + char(13) +
(case when @RECORDSOURCETYPE = 1 then ' 1 ' when @RECORDSOURCETYPE = 2 then ' 0 ' else
' (case when ''' + cast(@RECORDSOURCEID as nvarchar(36)) + ''' = [P].[SOURCEQUERYVIEWID] then 1 else 0 end) ' end) + '[ENABLEGOTO]' + char(13) +
'from dbo.[' + @PARENTTABLENAME + '] as [P]' + char(13) +
'inner join dbo.[' + @SEGMENTVIEWNAME + '] as [V] on [P].[' + @PARENTID + '] = [V].[ID]' + char(13);
--Filter the records, if specified
declare @TEMPFILTER nvarchar(255);
if @FILTER is not null and len(@FILTER) > 0
begin
set @TEMPFILTER = '%' + @FILTER + '%';
set @SQL = @SQL + 'where (' + @FULLNAMEFIELDNAME + ' like @FILTER' + char(13) +
' or ' + @FIRSTNAMEFIELDNAME + ' like @FILTER' + char(13) +
' or ' + @MIDDLENAMEFIELDNAME + ' like @FILTER' + char(13) +
' or ' + @LASTNAMEFIELDNAME + ' like @FILTER' + char(13) +
' or ' + @TITLEFIELDNAME + ' like @FILTER' + char(13) +
' or ' + @SUFFIXFIELDNAME + ' like @FILTER' + char(13) +
' or ' + @COUNTRYFIELDNAME + ' like @FILTER' + char(13) +
' or ' + @ADDRESSLINE1FIELDNAME + ' like @FILTER' + char(13) +
' or ' + @ADDRESSLINE2FIELDNAME + ' like @FILTER' + char(13) +
' or ' + @CITYFIELDNAME + ' like @FILTER' + char(13) +
' or ' + @STATEFIELDNAME + ' like @FILTER' + char(13) +
' or ' + @POSTCODEFIELDNAME + ' like @FILTER' + char(13) +
' or ' + @CARTFIELDNAME + ' like @FILTER' + char(13) +
' or ' + @DPCFIELDNAME + ' like @FILTER' + char(13) +
' or ' + @LOTFIELDNAME + ' like @FILTER' + char(13) +
' or ' + @PHONENUMBERFIELDNAME + ' like @FILTER' + char(13) +
' or ' + @EMAILADDRESSFIELDNAME + ' like @FILTER' + char(13) +
' or ' + @FIRSTGIFTDATEFIELDNAME + ' like @FILTER' + char(13) +
' or ' + @LOOKUPIDFIELDNAME + ' like @FILTER)' + char(13);
end
--Do NOT order the results here, for large amounts of data it takes too long for not much benefit.
exec sp_executesql @SQL, N'@FILTER nvarchar(255)', @FILTER = @TEMPFILTER;
end try
begin catch
exec dbo.[USP_RAISE_ERROR];
return 1;
end catch
return 0;