spGetAppealEmailStatus

        CREATE PROCEDURE [dbo].[spGetAppealEmailStatus]
@AppealID int

as

DECLARE @HasMail bit
DECLARE @HasSentTest bit
DECLARE @HasTargetLists bit

SET @HasSentTest = 0
SET @HasTargetLists  = 0
SET @HasMail = 0

IF EXISTS(SELECT NULL FROM EmailProjectAppeal_EmailList epael 
          INNER JOIN EmailProjectAppeal_ListEmail epale on epael.[ID] = epale.[EmailProjectAppealListID]
          WHERE epael.EmailProjectAppealID = @AppealID AND epael.Deleted = 0 AND epale.Deleted = 0)
    SET @HasMail = 1

IF EXISTS(SELECT NULL FROM EmailJob ej
          INNER JOIN Email e on ej.EmailID = e.[ID]
          INNER JOIN EmailProjectAppeal_ListEmail epale on e.ProjectAppealListEmailID = epale.[ID]
          INNER JOIN EmailProjectAppeal_EmailList epael on epael.[ID] = epale.[EmailProjectAppealListID]
          WHERE epael.EmailProjectAppealID = @AppealID AND 
          e.[Type] = 7 AND epale.Deleted = 0 AND epael.[Deleted] = 0)
   SET @HasSentTest = 1

IF EXISTS(SELECT NULL FROM EmailProjectAppeal_EmailList epae inner join EmailList el
on epae.EmailListID = el.ID WHERE epae.EmailProjectAppealID = @AppealID AND epae.AppealListType = 1 AND epae.Deleted = 0 AND el.AcquiredStatus = 1)
    SET @HasTargetLists = 1

SELECT @HasMail as HasEmails, @HasSentTest as HasSentTestMail, @HasTargetLists as HasTargetLists