declare @ownerId int = ; declare @ownerType varchar(3) = ; with fieldLookups as ( select selectionValue, selectionLabel, sortRank, case when fieldName = 'JobFunction' then 'OPR' when fieldName = 'CDRJobFunction' then 'CDR' when fieldName = 'BPLJobFunction' then 'BPL' else null end as partnerType from tbl_FSPRO_FieldLookups rr with (nolock) where rr.ownerId = @ownerId and rr.ownerType = @ownerType and rr.fieldName in ( ) ) , jobFunctions as ( select selectionValue as caJobFunction, selectionLabel as caJobFunctionText, partnerType, sortRank as rawSortRank, row_number() over( order by partnerType, sortRank, selectionLabel ) as sortRank from fieldLookups ) #forJSON( 'jobFunctions', attributes.datastore, 'sortRank' )#