declare @ownerId int; declare @ownerType varchar(3); set @ownerId = ; set @ownerType = ; with territoriesAndDistributors as ( SELECT cdr.cdr_dstcode, cdr.fsl_tablecode, cdr.ownerid, cdr.cdr_dstPath, cdr.cdr_recordId, cdr.cdr_dstName, cdr.cdr_dstcity, cdr.cdr_dststate, cdr.focusAccount, cdr.cdr_dstAddress1, cdr.cdr_dstAddress2, cdr.cdr_dstZip, cdr.classificationId, cdr.primaryContactId, cdr.fsl_dstId, -- pcdr.cdr_dstname parent_cdr_dstName, pcdr.cdr_recordId parent_cdr_recordId, pcdr.cdr_dstcompanytype parent_cdr_dstcompanytype, cdr.territoryId, cdr.territoryName, cdr.territoryPath, cdr.dstCompanyType as cdr_dstCompanyType, cdr.dstCompanyType, 'Y' crmActive, cdr.relatedTerritoryIds, cdr.mfr_bsr_id FROM tbl_DW_CRMDistributors#_tableSuffix# cdr with (nolock) WHERE cdr.fsl_tablecode = @ownerType AND cdr.ownerid = @ownerId and ( cdr.territoryId in ( ) or cdr.territoryId in ( select rt.relatedTerritoryId from tbl_TER_RelatedTerritories rt with (nolock) where rt.ownerId = @ownerId and rt.ownerType = @ownerType and rt.relationshipCode = 'CDR' and rt.territoryId in ( ) ) ) and cdr.territoryId in ( select rt.relatedTerritoryId from tbl_TER_RelatedTerritories rt with (nolock) where rt.ownerId = @ownerId and rt.ownerType = @ownerType and rt.relationshipCode = 'CDR' and rt.territoryId in ( ) ) ) #forJSON( 'territoriesAndDistributors', attributes.dataStore, 'cdr_dstPath' )#