declare @ownerId int = ; declare @ownerType varchar(3) = ; with noSaleReasons as ( select r.reasonId, r.reasonName, nullif( r.reasonDesc, '' ) reasonDesc, coalesce( nullif( r.reasonAbbr, '' ), r.reasonName ) reasonAbbr, r.requireComment, r.requireCompetitor, purchasingStatus, mapReasonId, sortRank, reasonPath, generalReason, detailReason, #attributes.ownerId#r.mfrId as mfr_id, #attributes.ownerId#mfrId mfrId from tbl_CRM_NoSaleReasons r with (nolock) where r.ownerId = @ownerId and r.ownerType = @ownerType and r.reasonType = 'GENERAL' and not exists ( select * from tbl_CRM_NOSaleReasons r1 with (nolock) where r1.parentReasonId = r.reasonId and r1.ownerId = r.ownerId and r1.ownertype = r.ownertype ) ) #forJSON( 'noSaleReasons', attributes.datastore, 'sortRank' )#