fnGetEmailsForUserByUserID

CREATE FUNCTION [dbo].[fnGetEmailsForUserByUserID]
(
    @UserID int
)
RETURNS TABLE AS RETURN

SELECT
    er.[ID] ID,
    case t.type
    when 1 then t.[Name]
    else e.[Name]
    end
    as Name,
    e.subject,
    r.MessageDate,
    r.SentDate,
    r.OpenedDate,
    r.RecentDSNDate,
    r.RecentDSNCategory,
    r.ID EmailJobRecipientID
FROM dbo.Email e
INNER JOIN EmailJob j ON j.EmailID = e.[ID] and j.[Current] = 1 and e.deleted = 0
INNER JOIN EmailTemplate t on t.id = e.EmailTemplateID
INNER JOIN Email_Recipient er on er.EmailID = e.ID AND er.UserID = @UserID
LEFT OUTER JOIN EmailJob_Recipient r ON r.EmailRecipientID = er.ID AND r.[Current] = 1
where e.Type <> 15 and e.Type <> 9

UNION

SELECT
    er.[ID] ID,
    case t.type
    when 1 then t.[Name]
    else e.[Name]
    end
    as Name,
    e.subject,
    r.MessageDate,
    r.SentDate,
    r.OpenedDate,
    r.RecentDSNDate,
    r.RecentDSNCategory,
    r.ID EmailJobRecipientID
FROM dbo.Email e
INNER JOIN EmailJob j ON j.EmailID = e.[ID] and j.[Current] = 1 and e.deleted = 0
INNER JOIN EmailTemplate t on t.id = e.EmailTemplateID
INNER JOIN BackOfficeSystemUsers bosu on bosu.ClientUsersID = @UserID
INNER JOIN Email_Recipient er on er.EmailID = e.ID AND er.BackOfficeSystemPeopleID = bosu.BackOfficePeopleID
LEFT OUTER JOIN EmailJob_Recipient r ON r.EmailRecipientID = er.ID AND r.[Current] = 1
where e.Type <> 15 and e.Type <> 9