declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @campaignId int = ; with rawData as ( select g.groupId, g.groupName, g.groupDescription, cast( case when cgl.groupId is not null then 1 else 0 end as bit ) recipientGroup, coalesce( c.category, 'Uncategorized Groups' ) category, coalesce( c.sortRank, max( c.sortRank ) over () + 1 ) categorySort, coalesce( c.categoryId, 0 ) categoryId, case when g.modvisibility like '%22%' then 1 else 0 end emailGroup from tbl_SEC_Groups g with (nolock) left outer join tbl_EML_CampaignGroupLnk cgl with (nolock) on cgl.groupId = g.groupId and cgl.campaignId = @campaignId left outer join tbl_SEC_Categories c with (nolock) on c.categoryId = g.categoryId and c.ownerId = g.ownerId and c.fsl_tablecode = g.fsl_tablecode where g.ownerId = @ownerId and g.fsl_tablecode = @ownerType ), data as ( select * from rawData where recipientGroup = 1 or emailGroup = 1 ) #forJSON( 'data', attributes.datastore, 'groupName' )#