select campaignType from tbl_EML_Campaign c with (nolock) where campaignId = and ownerId = and fsl_tablecode = declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @campaignId int = ; with targets as ( select distinct l.ownerId, l.ownerType, l.partnerType, m.orgId as partnerId, m.firstName, m.lastName, m.fspro_userId, m.email , case when m.failedEmailDate is not null then 'Yes' else 'No' end knownBadEmail from tbl_EML_Campaign c with (nolock) inner join tbl_LST_PartnerLists l with (nolock) on l.listId = c.targetListId and l.ownerId = c.ownerId and l.ownerType = c.fsl_tablecode inner join tbl_LST_PartnerListItems pli with (nolock) on pli.listId = l.listId inner join tbl_LST_PartnerListItemDetails pld with (nolock) on pld.listId = pli.listId and pld.partnerId = pli.partnerId inner join tbl_fspro_members m with (nolock) on m.ownerId = c.ownerId and m.fsl_tablecode = c.fsl_tablecode and m.fspro_userId = pld.detailId and m.orgId = pld.partnerId where c.campaignId = @campaignId and c.ownerId = @ownerId and c.fsl_tablecode = @ownerType ) , targetsWithPartners as ( select t.*, opr.companyName as organizationName from targets t with (nolock) inner join tbl_OPR_CLientOperators opr with (nolock) on opr.ownerId = t.ownerId and opr.fsltablecode = t.ownerType and opr.operatorId = t.partnerId where t.partnerType = 'OPR' union all select t.*, cdr.cdr_dstName as organizationName from targets t with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = t.ownerId and cdr.fsl_tablecode = t.ownerType and cdr.cdr_recordId = t.partnerId where t.partnerType = 'CDR' ) with targetsWithPartners as ( select m.fspro_userId, m.firstName, m.lastName, m.email, m.organization as organizationName from tbl_EML_Campaign c with (nolock) inner join tbl_EML_CampaignGroupLnk cg with (nolock) on cg.campaignId = c.campaignId inner join tbl_SEC_Groups g with (nolock) on g.groupId = cg.groupId inner join tbl_SEC_MemGrpLink mgl with (nolock) on mgl.groupId = g.groupId inner join tbl_fspro_members m with (nolock) on m.fspro_userId = mgl.fspro_userId where c.campaignId = @campaignId and c.ownerId = @ownerId and c.fsl_tablecode = @ownerType and m.emailYesNo = 1 ) , data as ( select fspro_userId, coalesce( nullif( firstName, '' ), 'blank' ) as firstName, coalesce( nullif( lastName, '' ), 'blank' ) lastName, coalesce( nullif( email, '' ), 'No Email Listed' ) as emailAddress, organizationName, knownBadEmail from targetsWithPartners ) #forJSON( 'data', attributes.datastore, 'firstName' )#