declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @objectiveId int = ; with data as ( select stf.linkId, stf.quota, stf.quotaAchieved, stf.insertType, mem.fspro_userId, mem.firstName + ' ' + mem.lastName as fullName, mem.title, mem.email, ter.name as territoryName, ter.territoryId, ter.territoryPath, case when coalesce( stf.territoryId, 0 ) = coalesce( obj.territoryId, 0 ) and stf.territoryId > 0 then 'Y' else 'N' end objectiveTerritory from tbl_bcrm_objectives obj with (nolock) INNER JOIN tbl_BCRM_ObjectiveStfLink stf with (nolock) on stf.objectiveid = obj.objectiveid and stf.ownerId = obj.ownerId and stf.ownerType = obj.ownerType left outer join tbl_TER_Territories ter with (nolock) on ter.ownerId = stf.ownerId and ter.fsl_tablecode = stf.ownerType and ter.territoryId = stf.territoryId inner join tbl_FSPro_Members mem with (nolock) on mem.ownerId = stf.ownerId and mem.fsl_tablecode= stf.ownerType and mem.fspro_userId = stf.fspro_userId WHERE ( obj.objectiveid = @objectiveId or obj.parentObjectiveId = @objectiveId ) and obj.ownerid = @ownerId and obj.ownertype = @ownerType ) #forJSON( 'data', attributes.datastore, 'fullName' )#