USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTATIONSEGMENT_MEMBER_EXPRESSION

The load procedure used by the view dataform template "Marketing Effort Segment Members Expression 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
@ISBBEC	bit	INOUT	Is BBEC?
@EFFORTID	uniqueidentifier	INOUT	Marketing effort ID
@EFFORTNAME	nvarchar(100)	INOUT	Marketing effort name
@PARENTSEGMENTID	uniqueidentifier	INOUT	Parent segment ID
@PARENTSEGMENTNAME	nvarchar(100)	INOUT	Parent segment name
@TESTSEGMENT	bit	INOUT	Is test segment

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MKTSEGMENTATIONSEGMENT_MEMBER_EXPRESSION]
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @MAILINGTYPECODE tinyint = null output,
  @ISBBEC bit = null output,
  @EFFORTID uniqueidentifier = null output,
  @EFFORTNAME nvarchar(100) = null output,
  @PARENTSEGMENTID uniqueidentifier = null output,
  @PARENTSEGMENTNAME nvarchar(100) = null output,
  @TESTSEGMENT bit = 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],
      @ISBBEC = dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTSEGMENT].[QUERYVIEWCATALOGID]),
      @EFFORTID = [MKTSEGMENTATION].[ID],
      @EFFORTNAME = [MKTSEGMENTATION].[NAME],
      @PARENTSEGMENTID = [MKTSEGMENT].[ID],
      @PARENTSEGMENTNAME = [MKTSEGMENT].[NAME]
    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],
      @ISBBEC = dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTSEGMENT].[QUERYVIEWCATALOGID]),
      @EFFORTID = [MKTSEGMENTATION].[ID],
      @EFFORTNAME = [MKTSEGMENTATION].[NAME],
      @PARENTSEGMENTID = [MKTSEGMENT].[ID],
      @PARENTSEGMENTNAME = [MKTSEGMENT].[NAME]
    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;