fnGetEmailCountForEmailTemplate

CREATE  FUNCTION dbo.fnGetEmailCountForEmailTemplate (@EmailTemplateID int)
    RETURNS int AS 
    BEGIN
        DECLARE @iCount int
        SELECT @iCount = COUNT(*) 
        FROM dbo.Email e
        INNER JOIN EmailJob j ON j.EmailID = e.[ID]
        WHERE e.EmailTemplateID = @EmailTemplateID
        AND j.[Current] = 1
        --AND j.status IN (4,6,7)        
        RETURN @iCount
    END