declare @ownerId int = ; declare @ownerType varchar(3) = ; with principals as ( select distinct bpl.principalId, mfr.mfr_name, mfr.mfr_id, coalesce( mfr.oneFSEnabled, 'N' ) oneFSEnabled, coalesce( bpl.mfrCallSharingEnabled, 'N' ) mfrCallSharingEnabled, bpl.customerManagerUserId, mem.firstName + ' ' + mem.lastName as salesMgrName from tbl_BRO_Principals bpl with (nolock) inner join tblManufacturers mfr with (nolock) on mfr.mfr_id = bpl.mfrId innerleft outer join tbl_BRO_PrincipalTerritories pt with (nolock) on pt.ownerId = bpl.ownerId and pt.ownerType = bpl.ownerType and pt.principalId = bpl.principalId and pt.hireStatus = 'A' inner join tbl_BRO_PrincipalTerritoryResources ptr on ptr.ownerId = pt.ownerId and ptr.ownerType = pt.ownerType and ptr.principalTerritoryId = pt.principalTerritoryId and ptr.resourceType = 'TER' and ptr.resourceId in ( ) inner join tbl_FSPro_Members mem with (nolock) on mem.fspro_userId = bpl.customerManagerUserId and mem.ownerId = bpl.ownerId and mem.FSL_TableCode = bpl.ownerType where bpl.ownerId = @ownerId and bpl.ownerType = @ownerType ) #forJSON( 'principals', attributes.datastore, 'mfr_name' )#