fnGetNextPreparedEmailJob

CREATE  function [dbo].[fnGetNextPreparedEmailJob] ()
returns int as 
begin

    declare @EMAILJOBID int
    select top 1 @EmailJobID = EJ.ID
    from dbo.EMAILJOB EJ (NOLOCK)
    left outer join dbo.EMAILTEMPLATE ET (NOLOCK) on EJ.EMAILTEMPLATEID = ET.ID
    left outer join dbo.DATASOURCES DS on DS.DATASOURCEID = ET.DATASOURCEID
    where EJ.[STATUS] in (2,13) and
              DS.[DATAPROCESSOR] is null and
          ((ISNULL(ET.DATASOURCEID,0) <> 320 )  -- not FAF Marketing emails
          OR ((ISNULL(ET.DATASOURCEID,0) = 320 ) AND EJ.SENDAFTERDATE <= GETDATE()))
          and ET.Type <> 1 --exclude Acknowledgments
          AND ET.Type <> 14 -- exclude eCards
          and ET.TYPE <> 5 -- exclude Chapter emails
          and ET.TYPE <> 15 -- exclude test emails
          and ET.TYPE <> 13 -- exclude notification emails
    order by EJ.CREATEDATE asc
    --2 Prepared Jobs
    --13 Processed Jobs that failed during upload

    if @@ROWCOUNT = 0 
          select @EmailJobID = 0

    return @EmailJobID
end