USP_MKTSEGMENTATIONACTIVATEPROCESS_GETRECENTSTATUSINFO

CREATE procedure dbo.USP_MKTSEGMENTATIONACTIVATEPROCESS_GETRECENTSTATUSINFO
(
    @SEGMENTATIONID uniqueidentifier
)
as
begin
    declare @STATUSID uniqueidentifier;
    declare @STATUSCODE integer = -1;
    declare @STATUS nvarchar(25) = '';
    declare @STATUSMESSAGE nvarchar(max) = '';
    declare @ERRORMESSAGE nvarchar(max) = '';
    declare @EMAILQUEUED bit = 0;
    declare @EMAILSENDDATE datetime;

    select top 1
        @STATUSID = BUSINESSPROCESSSTATUS.ID,
        @STATUSCODE = BUSINESSPROCESSSTATUS.STATUSCODE,
        @STATUS = BUSINESSPROCESSSTATUS.STATUS,
        @ERRORMESSAGE = coalesce(BUSINESSPROCESSSTATUS.ERRORMESSAGE, ''),
        @EMAILQUEUED = case when APPEALMAILINGACTIVATEEMAILJOBSCHEDULE.ID is null then 0 else 1 end,
        @EMAILSENDDATE = APPEALMAILINGACTIVATEEMAILJOBSCHEDULE.EMAILJOBSTART
    from dbo.MKTSEGMENTATIONACTIVATEPROCESS
    inner join dbo.MKTSEGMENTATIONACTIVATEPROCESSSTATUS 
        on MKTSEGMENTATIONACTIVATEPROCESSSTATUS.PARAMETERSETID = MKTSEGMENTATIONACTIVATEPROCESS.ID
    inner join dbo.BUSINESSPROCESSSTATUS 
        on BUSINESSPROCESSSTATUS.ID = MKTSEGMENTATIONACTIVATEPROCESSSTATUS.ID
    left outer join dbo.APPEALMAILINGACTIVATEEMAILJOBSCHEDULE
        on MKTSEGMENTATIONACTIVATEPROCESS.ID = APPEALMAILINGACTIVATEEMAILJOBSCHEDULE.ID
    where MKTSEGMENTATIONACTIVATEPROCESS.SEGMENTATIONID = @SEGMENTATIONID
    order by BUSINESSPROCESSSTATUS.STARTEDON desc;

    if @STATUSCODE = 1
        begin
            declare @SQL nvarchar(max) = 'select @STATUSMESSAGE = STATUSMESSAGE from ##BUSINESSPROCESSSTATUS_' + replace(cast(@STATUSID as nvarchar(36)),'-','_');

            exec sp_executesql @SQL, N'@STATUSMESSAGE nvarchar(max) OUTPUT', @STATUSMESSAGE OUTPUT;
        end

    select
        @STATUSCODE as STATUSCODE,
        @STATUS as STATUS,
        @STATUSMESSAGE as STATUSMESSAGE,
        @ERRORMESSAGE as ERRORMESSAGE,
        @EMAILQUEUED as EMAILQUEUED,
        @EMAILSENDDATE as EMAILSENDDATE;

end