USP_DATAFORMTEMPLATE_VIEW_UNIQUE_MKTSEGMENTATION_FROM_APPEAL

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_UNIQUE_MKTSEGMENTATION_FROM_APPEAL]
(
  @ID uniqueidentifier, -- appeal id
  @DATALOADED bit = 0 output,
  @SEGMENTATIONID uniqueidentifier = null output
)
as
  set nocount on;

  declare @EFFORTCOUNT int = 0;

  select @EFFORTCOUNT = count(*) 
  from dbo.[MKTSEGMENTATION]
  inner join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  where 
    [MKTSEGMENTATION].[ACTIVE] = 1 
    and nullif([MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID], '') = @ID;

  /* Select marketing effort id if there is only one marketing effort associated with appeal */
  if @EFFORTCOUNT = 1
    begin

      select @SEGMENTATIONID = [MKTSEGMENTATION].[ID]
      from dbo.[MKTSEGMENTATION]
      inner join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
      where 
        [MKTSEGMENTATION].[ACTIVE] = 1 
        and nullif([MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID], '') = @ID;

    end

  set @DATALOADED = 1;

  return 0;