UFN_MKTSEGMENTATIONEXPORTPROCESSOUTPUT_GETEMAILJOBSTART

Returns the email job start date/time from the status detail for a particular marketing effort export business process output.

Return

Return Type
datetime

Parameters

Parameter	Parameter Type	Mode	Description
@BUSINESSPROCESSOUTPUTID	uniqueidentifier	IN

Definition

Copy



CREATE function dbo.[UFN_MKTSEGMENTATIONEXPORTPROCESSOUTPUT_GETEMAILJOBSTART]
(
  @BUSINESSPROCESSOUTPUTID uniqueidentifier
)
returns datetime
as
  begin
    declare @BUSINESSPROCESSSTATUSID uniqueidentifier;
    declare @TABLEKEY nvarchar(50);
    declare @SUMMARYTABLENAME nvarchar(255);
    declare @SQL nvarchar(max);
    declare @EMAILJOBSTARTTEXT nvarchar(20);
    declare @EMAILJOBSTART datetime;

    select 
      @BUSINESSPROCESSSTATUSID = [BUSINESSPROCESSSTATUSID], 
      @TABLEKEY = [TABLEKEY]
    from dbo.[BUSINESSPROCESSOUTPUT] 
    where [ID] = @BUSINESSPROCESSOUTPUTID;

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

    set @SQL =
      'select ' + 
      '  @EMAILJOBSTARTTEXT = [STATUS].value(''(/STATUS/@EmailJobStart)[1]'', ''nvarchar'') ' +
      'from dbo.[' + @SUMMARYTABLENAME + '] ' +
      'where [TABLEKEY] = @TABLEKEY';

    exec sp_executesql @SQL, N'@EMAILJOBSTARTTEXT nvarchar(20) output, @TABLEKEY nvarchar(50)', @EMAILJOBSTARTTEXT = @EMAILJOBSTARTTEXT output, @TABLEKEY = @TABLEKEY;

    if len(isnull(@EMAILJOBSTARTTEXT, '')) = 0
      set @EMAILJOBSTART = null
    else
      set @EMAILJOBSTART = convert(datetime, @EMAILJOBSTART);

    return @EMAILJOBSTART;
  end;