USP_EMAILJOB_GETPREPAREDJOB

CREATE procedure dbo.USP_EMAILJOB_GETPREPAREDJOB
as
begin

     declare @EJGUID nvarchar(36) = null;
    declare @EJID int;

    select top 1 @EJID = EJ.ID, @EJGUID = upper(cast(EJ.GUID as nvarchar(36)))
    from dbo.EMAILJOB EJ    
    inner join dbo.EMAIL E on E.ID = EJ.EMAILID
    where EJ.[STATUS] in (2,13)
    --2 Prepared Jobs

    --13 Processed Jobs that failed during upload

    and E.[DELETED] = 0
    and EJ.[CURRENT] = 1
    and EJ.[SENDAFTERDATE] <= GETUTCDATE()
    order by EJ.[CREATEDATE] asc;


    if @EJGUID is not  null
    begin
            select top 1 
                EJ.ID,
                EJ.NAME,
                EJ.EMAILID,
                EJ.GUID,
                EJ.CONTENTHTML, 
                EJ.CONTENTTEXT, 
                EJ.FROMADDRESS, 
                EJ.FROMDISPLAYNAME,
                EJ.SUBJECT, 
                EJ.REPLYADDRESS, 
                EJ.RETURNRECEIPT, 
                EJ.RETURNRECEIPTADDRESS, 
                EJ.FORWARDDSN, 
                EJ.FORWARDDSNADDRESS,
                EJ.XMLDATASETADDRESSEES,
                ISNULL(APPEALDS.DATAPROCESSOR, DS.DATAPROCESSOR) as DATAPROCESSOR,
                E.TYPE,
                E.CLIENTSITESID,
                ET.EXPORTDEFINITIONID,
                E.EMAILADDRESSTYPEID,
                E.ALTERNATESUBJECT
            from dbo.EMAILJOB EJ
            inner join dbo.EMAILTEMPLATE ET on ET.ID = EJ.EMAILTEMPLATEID
            left outer join dbo.DATASOURCES DS on DS.DATASOURCEID = ET.DATASOURCEID
            inner join dbo.EMAIL E on E.ID = EJ.EMAILID
            left outer join dbo.EmailProjectAppeal_ListEmail EPA_LE on EPA_LE.ID = E.ProjectAppealListEmailID
            left outer join dbo.EmailProjectAppeal_EmailList EPA_EL on EPA_EL.ID = EPA_LE.EmailProjectAppealListID
            left outer join dbo.EmailList EL on EL.ID = EPA_EL.EmailListID
            left outer join dbo.DataSources APPEALDS on APPEALDS.DataSourceID = EL.DataSourceID
            where EJ.ID = @EJID;
        end      
end