DECLARE
@AdvertiserId INT,
@DirtyReason INT

SET @advertiserid = 3
SET @dirtyreason = 7

BEGIN


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   --DROP TABLE #temparraytable;

CREATE TABLE #tempArrayTable (advertiserHierarchyId int)
    INSERT INTO #tempArrayTable (advertiserHierarchyId)
    (SELECT convert(int,str) FROM dbo.SplitString('14167',','))

CREATE INDEX #ix_tempArrayTable ON #tempArrayTable  (advertiserHierarchyId)

        SELECT DISTINCT MA.ContentNetwork,MA.DestinationURL, MA.MasterAdGroupId, AA.Title, MA.ShortTitle, ma.LinkedObjectId, ma.LinkedObjectTypeId, MA.MasterHierarchyId,
                AA.AdvertiserAdGroupId, AA.AdvertiserId, AA.AdvertiserHierarchyId, AdvertiserAdGroupCode, AA.AdvertiserHierarchyId, AA.Dirty,
                AA.Deleted, AA.Paused, MA.MatchTypeId,
                lob.MaxCostPerClick AS 'LinkedObjectROIMAXCPC'
          FROM admanMasterAdGroup_tbl MA
    INNER JOIN admanAdvertiserAdGroup_tbl AA  ON MA.MasterAdGroupId = AA.MasterAdGroupId
        INNER JOIN admanAdvertiserCreative_tbl AC ON AA.AdvertiserAdGroupId = AC.AdvertiserAdGroupId
        INNER JOIN admanAdvertiserHierarchy_tbl AH ON AA.AdvertiserHierarchyId = AH.AdvertiserHierarchyId
        INNER JOIN #tempArrayTable tat ON tat.advertiserHierarchyId = AA.AdvertiserHierarchyId
    LEFT JOIN admanLinkedObjectROIBid_tbl lob ON lob.LinkedObjectId = ma.LinkedObjectId AND lob.LinkedObjectTypeId = ma.LinkedObjectTypeId AND lob.AdvertiserId = @AdvertiserId
        WHERE AA.AdvertiserId = @AdvertiserId
          **AND (AC.Dirty & @dirtyReason > 0)**
      AND NOT (AC.Deleted = 1 AND AC.AdvertiserCreativeCode IS NULL)
          AND AA.AdvertiserAdGroupCode IS NOT NULL
          AND AA.Error = 0
          AND AC.Error = 0

        SELECT  MCH.Headline,MCH.Duplicate,MCH.MasterCreativeHeadlineId, MCB.MasterCreativeBodyId,
                ACI.AdvertiserCreativeInstanceId,ACI.ActualLine1,ACI.ActualLine2,ACI.ActualHeadline,ACI.ActualDisplayURL,ACI.ActualDestinationURL, MCU.MasterCreativeURLId, MCH.Rank,
                MCB.Line1,
                MCB.Line2, AA.MasterAdGroupId, AdvertiserCreativeId,
                AA.AdvertiserAdGroupId, AC.Dirty, AC.Deleted, AC.Paused, MCU.DisplayURL,AA.AdvertiserId, AC.AdvertiserCreativeCode,
                MCH.CreativeHeadlineTemplateId, MCB.CreativeBodyTemplateId, MCU.CreativeURLTemplateId, CBT.CreativeTemplateGroupId
           FROM admanAdvertiserCreative_tbl AC
     INNER JOIN admanMasterCreativeHeadline_tbl MCH  ON AC.MasterCreativeHeadlineId = MCH.MasterCreativeHeadlineId
     INNER JOIN admanAdvertiserCreativeInstance_tbl ACI  ON AC.AdvertiserCreativeInstanceId = ACI.AdvertiserCreativeInstanceId
     INNER JOIN admanMasterCreativeBody_tbl MCB  ON MCB.MasterCreativeBodyId = AC.MasterCreativeBodyId
     INNER JOIN admanMasterCreativeURL_tbl MCU  ON AC.MasterCreativeURLId = MCU.MasterCreativeURLId
     INNER JOIN admanAdvertiserAdGroup_tbl AA  ON AC.AdvertiserAdGroupId = AA.AdvertiserAdGroupId
     INNER JOIN admanAdvertiserHierarchy_tbl AH  ON AA.AdvertiserHierarchyId = AH.AdvertiserHierarchyId
     INNER JOIN admanCreativeBodyTemplate_tbl CBT  ON CBT.CreativeBodyTemplateId = MCB.CreativeBodyTemplateId
     INNER JOIN #tempArrayTable tat ON tat.advertiserHierarchyId = AA.AdvertiserHierarchyId
          WHERE AA.AdvertiserId = @AdvertiserId
           **AND (AC.Dirty & @dirtyreason > 0)**
           AND NOT (AC.Deleted = 1 AND AC.AdvertiserCreativeCode IS NULL)
           AND AA.AdvertiserAdGroupCode IS NOT NULL
           AND AA.Error = 0
           AND AC.Error = 0
      ORDER BY AC.Deleted desc, AC.AdvertiserAdGroupId,AC.AdvertiserCreativeId asc

END;

BEGIN

DROP TABLE #temparraytable2;
CREATE TABLE #tempArrayTable2 (advertiserHierarchyId int)
INSERT INTO #tempArrayTable2 (advertiserHierarchyId)
    (SELECT convert(int,str) FROM dbo.SplitString('14167',','))
CREATE INDEX #ix_tempArrayTable ON #tempArrayTable2 (advertiserHierarchyId)

        SELECT DISTINCT MA.ContentNetwork,MA.DestinationURL, MA.MasterAdGroupId, AA.Title, MA.ShortTitle, ma.LinkedObjectId, ma.LinkedObjectTypeId, MA.MasterHierarchyId,
                AA.AdvertiserAdGroupId, AA.AdvertiserId, AA.AdvertiserHierarchyId, AdvertiserAdGroupCode, AA.AdvertiserHierarchyId, AA.Dirty,
                AA.Deleted, AA.Paused, MA.MatchTypeId,
                lob.MaxCostPerClick AS 'LinkedObjectROIMAXCPC'
          FROM admanMasterAdGroup_tbl MA
    INNER JOIN admanAdvertiserAdGroup_tbl AA  ON MA.MasterAdGroupId = AA.MasterAdGroupId
        INNER JOIN admanAdvertiserCreative_tbl AC ON AA.AdvertiserAdGroupId = AC.AdvertiserAdGroupId
        INNER JOIN admanAdvertiserHierarchy_tbl AH ON AA.AdvertiserHierarchyId = AH.AdvertiserHierarchyId
        INNER JOIN #tempArrayTable2 tat ON tat.advertiserHierarchyId = AA.AdvertiserHierarchyId
    LEFT JOIN admanLinkedObjectROIBid_tbl lob ON lob.LinkedObjectId = ma.LinkedObjectId AND lob.LinkedObjectTypeId = ma.LinkedObjectTypeId AND lob.AdvertiserId = @AdvertiserId
        WHERE AA.AdvertiserId = @AdvertiserId
          **AND (AC.Dirty > 0 AND @dirtyReason > 0)**
      AND NOT (AC.Deleted = 1 AND AC.AdvertiserCreativeCode IS NULL)
          AND AA.AdvertiserAdGroupCode IS NOT NULL
          AND AA.Error = 0
          AND AC.Error = 0

        SELECT  MCH.Headline,MCH.Duplicate,MCH.MasterCreativeHeadlineId, MCB.MasterCreativeBodyId,
                ACI.AdvertiserCreativeInstanceId,ACI.ActualLine1,ACI.ActualLine2,ACI.ActualHeadline,ACI.ActualDisplayURL,ACI.ActualDestinationURL, MCU.MasterCreativeURLId, MCH.Rank,
                MCB.Line1,
                MCB.Line2, AA.MasterAdGroupId, AdvertiserCreativeId,
                AA.AdvertiserAdGroupId, AC.Dirty, AC.Deleted, AC.Paused, MCU.DisplayURL,AA.AdvertiserId, AC.AdvertiserCreativeCode,
                MCH.CreativeHeadlineTemplateId, MCB.CreativeBodyTemplateId, MCU.CreativeURLTemplateId, CBT.CreativeTemplateGroupId
           FROM admanAdvertiserCreative_tbl AC
     INNER JOIN admanMasterCreativeHeadline_tbl MCH  ON AC.MasterCreativeHeadlineId = MCH.MasterCreativeHeadlineId
     INNER JOIN admanAdvertiserCreativeInstance_tbl ACI  ON AC.AdvertiserCreativeInstanceId = ACI.AdvertiserCreativeInstanceId
     INNER JOIN admanMasterCreativeBody_tbl MCB  ON MCB.MasterCreativeBodyId = AC.MasterCreativeBodyId
     INNER JOIN admanMasterCreativeURL_tbl MCU  ON AC.MasterCreativeURLId = MCU.MasterCreativeURLId
     INNER JOIN admanAdvertiserAdGroup_tbl AA  ON AC.AdvertiserAdGroupId = AA.AdvertiserAdGroupId
     INNER JOIN admanAdvertiserHierarchy_tbl AH  ON AA.AdvertiserHierarchyId = AH.AdvertiserHierarchyId
     INNER JOIN admanCreativeBodyTemplate_tbl CBT  ON CBT.CreativeBodyTemplateId = MCB.CreativeBodyTemplateId
     INNER JOIN #tempArrayTable2 tat ON tat.advertiserHierarchyId = AA.AdvertiserHierarchyId
          WHERE AA.AdvertiserId = @AdvertiserId
           **AND (AC.Dirty > 0 AND @dirtyreason > 0)**
           AND NOT (AC.Deleted = 1 AND AC.AdvertiserCreativeCode IS NULL)
           AND AA.AdvertiserAdGroupCode IS NOT NULL
           AND AA.Error = 0
           AND AC.Error = 0
      ORDER BY AC.Deleted desc, AC.AdvertiserAdGroupId,AC.AdvertiserCreativeId asc

END

FROM
  dbo.admanAdvertiserAdGroup_tbl AS AA
INNER JOIN
  dbo.admanAdvertiserCreative_tbl AS AC
    ON AA.AdvertiserAdGroupId = AC.AdvertiserAdGroupId
    AND (AC.Dirty & @dirtyReason > 0)
INNER JOIN
  dbo.admanMasterAdGroup_tbl AS MA
    ON MA.MasterAdGroupId = AA.MasterAdGroupId
...
WHERE AA.AdvertiserId = @AdvertiserId
...
OPTION (FORCE ORDER);