USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTATIONSEGMENT_MEMBER

The load procedure used by the view dataform template "Marketing Effort Segment Members View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@MAILINGTYPECODE	tinyint	INOUT	Marketing effort type code
@SEGMENTNAME	nvarchar(100)	INOUT	Segment
@SEGMENTSEQUENCE	int	INOUT	Segment number
@EXCLUDE	bit	INOUT	Is inline exclusion
@TESTSEGMENT	bit	INOUT	Is test segment
@TESTSEGMENTNAME	nvarchar(200)	INOUT	Test segment
@TESTSEGMENTSEQUENCE	int	INOUT	Test segment sequence

Definition

Copy



create procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTATIONSEGMENT_MEMBER]
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @MAILINGTYPECODE tinyint = null output,
  @SEGMENTNAME nvarchar(100) = null output,
  @SEGMENTSEQUENCE integer = null output,
  @EXCLUDE bit = null output,
  @TESTSEGMENT bit = null output,
  @TESTSEGMENTNAME nvarchar(200) = null output,
  @TESTSEGMENTSEQUENCE integer = null output
)
as
  set nocount on;

  set @DATALOADED = 0;

  set @TESTSEGMENT = (case when exists(select top 1 1 from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = @ID) then 1 else 0 end);

  if @TESTSEGMENT = 0
    select
      @DATALOADED = 1,
      @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
      @SEGMENTNAME = [MKTSEGMENT].[NAME],
      @SEGMENTSEQUENCE = [MKTSEGMENTATIONSEGMENT].[SEQUENCE],
      @EXCLUDE = [MKTSEGMENTATIONSEGMENT].[EXCLUDE],
      @TESTSEGMENTNAME = '',
      @TESTSEGMENTSEQUENCE = 0
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
    where [MKTSEGMENTATIONSEGMENT].[ID] = @ID;
  else
    select
      @DATALOADED = 1,
      @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
      @SEGMENTNAME = [MKTSEGMENT].[NAME],
      @SEGMENTSEQUENCE = [MKTSEGMENTATIONSEGMENT].[SEQUENCE],
      @EXCLUDE = 0,
      @TESTSEGMENTNAME = dbo.[UFN_MKTSEGMENTATIONTESTSEGMENT_GETNAME](@ID),
      @TESTSEGMENTSEQUENCE = [MKTSEGMENTATIONTESTSEGMENT].[SEQUENCE] - 1
    from dbo.[MKTSEGMENTATIONTESTSEGMENT]
    inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].SEGMENTID = [MKTSEGMENTATIONSEGMENT].ID
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
    where [MKTSEGMENTATIONTESTSEGMENT].[ID] = @ID;

  return 0;