USP_DATALIST_MKTSELECTIONBRIEFSEGMENTATIONSEGMENTSELECTION

Returns a list of all selections in all segments of a marketing effort.

Parameters

Parameter	Parameter Type	Mode	Description
@SEGMENTATIONID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.

Definition

Copy



CREATE procedure dbo.[USP_DATALIST_MKTSELECTIONBRIEFSEGMENTATIONSEGMENTSELECTION]
(
  @SEGMENTATIONID uniqueidentifier
)
as
  set nocount on;

  select
    [MKTSEGMENTATIONSEGMENT].[ID] as [SEGMENTID],
    [MKTSEGMENTSELECTION].[ID] as [SELECTIONID],
    [IDSETREGISTER].[NAME] as [SELECTIONNAME],
    [IDSETREGISTER].[DESCRIPTION] as [SELECTIONDESCRIPTION]
  from dbo.[MKTSEGMENTATION]
  inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  inner join dbo.[MKTSEGMENTSELECTION] on [MKTSEGMENTSELECTION].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
  inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENTSELECTION].[SELECTIONID]
  where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID

  union

  -- also return regular segments which have no selections

  -- don't return vendor managed list segments though


  select
    [MKTSEGMENTATIONSEGMENT].[ID] as [SEGMENTID],
    [MKTSEGMENTSELECTION].[ID] as [SELECTIONID],
    null as [SELECTIONNAME],
    null as [SELECTIONDESCRIPTION]
  from dbo.[MKTSEGMENTATION]
  inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left outer join dbo.[MKTSEGMENTSELECTION] on [MKTSEGMENTSELECTION].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
  where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID 
  and [MKTSEGMENTSELECTION].[ID] is null 
  and dbo.[UFN_MKTSEGMENT_ISVENDORMANAGEDLIST]([MKTSEGMENTATIONSEGMENT].[SEGMENTID]) = 0;  

  return 0;