UFN_MKTSEGMENTATIONEXPORTPROCESSOUTPUT_SUMMARYSTATUSCODE

CREATE function dbo.[UFN_MKTSEGMENTATIONEXPORTPROCESSOUTPUT_SUMMARYSTATUSCODE]
(
  @STATUS xml
)
returns tinyint
as
  begin
    declare @STATUSCODE tinyint;

    declare @EMAILSTATUS table ([STATUSCODE] tinyint not null);

    insert into @EMAILSTATUS
      select distinct T.c.value('(STATUSCODE)[1]', 'tinyint')
      from @STATUS.nodes('/STATUS/ITEM') T(c)
      where T.c.value('(NETCOMMUNITYEMAILID)[1]', 'integer') > 0
      and T.c.value('(STATUSCODE)[1]', 'tinyint') > 0;

    if (select count(*) from @EMAILSTATUS) = 0
      set @STATUSCODE = isnull(@STATUS.value('(/STATUS/@StatusCode)[1]', 'tinyint'), 0)
    else 
      if exists (select top 1 1 from @EMAILSTATUS where [STATUSCODE] = 1)
        set @STATUSCODE = 1 -- pending

      else -- [STATUSCODE] is 2 (success) or 3 or 4 (failure); failure trumps success

        select @STATUSCODE = max([STATUSCODE]) from @EMAILSTATUS;

    return @STATUSCODE;
  end;