fnEmailsForList

CREATE FUNCTION [dbo].[fnEmailsForList](@ListID int) 
RETURNS TABLE AS RETURN

select 
    e.Id,
    e.EmailTemplateID,
    e.Name,
    e.Subject,
    e.Description,
    'TemplateName' =
        CASE
            WHEN (et.[Deleted] = 1)
                AND LEN(et.name) > 42
                AND CHARINDEX('-', RIGHT(et.name, 42)) = 1
                AND CHARINDEX('-', RIGHT(et.name, 33)) = 1
                THEN LEFT(et.name, (LEN(et.name) - 42)) + ' (Deleted)'
            ELSE
                et.[name]
        END,
    e.OwnerID,
    e.Guid,
    e.Type,
    ej.Status,
    e.FromAddress,
    e.FromDisplayName,
    e.SendAfterDate,
    e.CreateDate,
    e.ParentEmailID,
    e.ProjectAppealListEmailID,
    eml.IsTest,
    e.ClientSitesId
from email_emaillist eml
    inner join email e

   on e.id = eml.emailid
    inner join emailtemplate et 
    on et.id = e.emailtemplateid
    left outer join EmailJob EJ on E.ID=EJ.EmailID and EJ.ConditionalParentJobID is NULL
where e.deleted=0
and emaillistid=@ListID