USP_MKTSEGMENTATIONEXPORTPROCESS_GETSUMMARYEMAILJOBSTATUS

Retrieves information about status of all email jobs associated with a marketing effort export.

Parameters

Parameter	Parameter Type	Mode
@BUSINESSPROCESSSTATUSID	uniqueidentifier	IN
@NETCOMMUNITYEMAILJOBSTOSTART	bit	INOUT
@NETCOMMUNITYEMAILJOBSPENDING	bit	INOUT

Definition

Copy



create procedure dbo.[USP_MKTSEGMENTATIONEXPORTPROCESS_GETSUMMARYEMAILJOBSTATUS]
(
  @BUSINESSPROCESSSTATUSID uniqueidentifier,
  @NETCOMMUNITYEMAILJOBSTOSTART bit = 0 output,
  @NETCOMMUNITYEMAILJOBSPENDING bit = 0 output
)
as
  set nocount on;

  declare @TABLENAME nvarchar(250);
  declare @SQL nvarchar(max);

  select @TABLENAME = [TABLENAME] from dbo.[BUSINESSPROCESSOUTPUT] where [BUSINESSPROCESSSTATUSID] = @BUSINESSPROCESSSTATUSID and upper([TABLEKEY]) = upper(convert(nvarchar(36), @BUSINESSPROCESSSTATUSID));

  set @SQL =
      'declare @EMAILSTATUS table ([STATUSCODE] tinyint not null);' + char(13) +
      'set @NETCOMMUNITYEMAILJOBSTOSTART = 0;' + char(13) +
      'set @NETCOMMUNITYEMAILJOBSPENDING = 0;' + char(13) +
      'insert into @EMAILSTATUS' + char(13) +
      'select' + char(13) +
      '  dbo.[UFN_MKTSEGMENTATIONEXPORTPROCESSOUTPUT_SUMMARYSTATUSCODE]([BUSINESSPROCESSOUTPUTSUMMARY].[STATUS]) as [STATUSCODE]' + char(13) +
      'from dbo.[' + @TABLENAME + '] as [BUSINESSPROCESSOUTPUTSUMMARY]' + char(13) +
      'where exists (' + char(13) +
      '    select top 1 1' + char(13) +
      '    from [BUSINESSPROCESSOUTPUTSUMMARY].[PACKAGES].nodes(''/PACKAGES/ITEM'') T(c)' + char(13) +
      '    inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = T.c.value(''(ID)[1]'', ''uniqueidentifier'')' + char(13) +
      '    where [MKTPACKAGE].[NETCOMMUNITYTEMPLATEID] > 0);' + char(13) +
      'if exists (select top 1 1 from @EMAILSTATUS where [STATUSCODE] = 1)' + char(13) +
      '  set @NETCOMMUNITYEMAILJOBSPENDING = 1;' + char(13) +
      'if exists (select top 1 1 from @EMAILSTATUS where [STATUSCODE] = 0 or [STATUSCODE] = 5 or [STATUSCODE] = 6 or [STATUSCODE] = 4)' + char(13) +
      '  set @NETCOMMUNITYEMAILJOBSTOSTART = 1;';

  exec sp_executesql @SQL, N'@NETCOMMUNITYEMAILJOBSTOSTART bit output, @NETCOMMUNITYEMAILJOBSPENDING bit output', @NETCOMMUNITYEMAILJOBSTOSTART = @NETCOMMUNITYEMAILJOBSTOSTART output, @NETCOMMUNITYEMAILJOBSPENDING = @NETCOMMUNITYEMAILJOBSPENDING output;

  return 0;