fnEmailListIsLinked

            CREATE  FUNCTION [dbo].[fnEmailListIsLinked]
(
    @EmailListID int
)
RETURNS bit
AS
BEGIN
    DECLARE @IsLinked bit
    SET @IsLinked=0

    --Check link to template if I'm a newsletter list
    IF EXISTS (
        SELECT TOP 1 t.[ID]
        FROM [EmailTemplate] t
        INNER JOIN EmailTemplate_EmailList etel ON etel.EmailTemplateID = t.[ID]
        WHERE etel.EmailListID = @EmailListID
        AND t.[deleted] = 0
        AND t.type = 2
    )
    SET @IsLinked=1

    --Check link to template if I'm uploaded list
    if exists (
        SELECT TOP 1 t.[ID]
        FROM [EmailTemplate] t
        inner join Emaillist el on
        t.datasourceid=el.uploadeddatasourceid and el.uploadeddatasourceid > 0
        where el.id = @EmailListID
        and t.deleted=0
    )
    SET @IsLinked=1

    --check if linked to any emails
    if exists (
        select top 1 eml.id
        from email_emaillist eml
        inner join email e
        on eml.emailid = e.id
        where emaillistid=@EmailListID
        and e.deleted=0 and e.type <> 15 AND ISNULL(e.ProjectAppealListEmailID,-1) = -1
    )
    SET @IsLinked=1

    --check if linked to eCampaigns Must check for deleted flag at every level
    IF EXISTS(
        SELECT TOP 1 el.ID 
        FROM EmailList el
        INNER JOIN EmailProjectAppeal_EmailList epael on el.ID = epael.EmailListID
        INNER JOIN EmailProjectAppeal epa on epael.EmailProjectAppealID = epa.ID
        INNER JOIN EmailProject ep on epa.EmailProjectID = ep.ID
        WHERE el.ID = @EmailListID AND (epael.Deleted = 0 AND epa.Deleted = 0 AND ep.Deleted = 0))
    SET @IsLinked=1

    --check if linked to conditional content in a message or template that isn't deleted
    IF EXISTS(
        select top 1 1 from dbo.EmailList EL
        left outer join dbo.ConditionList CL on CL.EmailListID = EL.ID
        left outer join dbo.ConditionBlockCondition CBC on CBC.ID = CL.ConditionBlockConditionID
        left outer join dbo.EmailConditionBlock ECB on ECB.ConditionBlockID = CBC.ConditionBlockID
        left outer join dbo.Email E on E.ID = ECB.EmailID
        left outer join dbo.EmailTemplateConditionBlock ETCB on ETCB.ConditionBlockID = CBC.ConditionBlockID
        left outer join dbo.EmailTemplate ET on ET.ID = ETCB.EmailTemplateID
        where EL.ID = @EmailListID and (ET.Deleted = 0 OR E.Deleted = 0)                
    )
    SET @IsLinked=1


    RETURN @IsLinked
END