select coalesce( nullif( primaryChannel, '' ), 'FOODSERVICE' ) as primaryChannel from tbl_Fspro_members with (nolock) where fspro_userId = declare @ownerId as int; declare @ownerType as varchar(3); declare @channel as varchar(max) = ; set @ownerId = ; set @ownerType = ; with sections as ( select s.sectionId, ltrim( cast( s.[name] as varchar(max))) as [text], ltrim( coalesce( s.uri, '' )) uri, ltrim( coalesce( s.requiredPermission, '' )) requiredPermission, ltrim( coalesce( s.menuSelectionCode, '' )) menuSelectionCode, s.displayRank, ltrim( coalesce( s.hrefTarget, '' )) hrefTarget, ltrim( coalesce( s.onClick, '' )) as clickJS, ltrim( coalesce( s.channel, '' )) as channel, s.parentSectionId, '#_contentRoot#/' + img.mainFolder + '/' + img.subFolder + '/' + img.fileName as imageSrc, case when img.contentId is not null then 1 else 0 end as hasImage from fsenablers..tbl_CUS_WebSites w with (nolock) inner join fsenablers..tbl_CUS_WebsiteSections s with (nolock) on s.webSiteId = w.webSiteId left outer join tbl_FSC_Content img with (nolock) on img.contentId = s.navigationImageContentId where w.portal_ownerId = @ownerId and w.portal_ownerType = @ownerType and s.live = 'Y' and coalesce( s.channel, @channel ) = @channel ) select text, uri, requiredPermission, menuSelectionCode, displayRank, hrefTarget, clickJS, channel, 1 as closeMenuOnClick, hasImage, imageSrc , ( select sub.text, sub.uri, sub.requiredPermission, sub.menuSelectionCode, sub.displayRank, sub.hrefTarget, sub.clickJS, sub.channel, 1 as closeMenuOnClick, sub.hasImage, sub.imageSrc from sections sub where sub.parentSectionId = main.sectionId order by sub.displayRank, sub.text for json auto, include_null_values ) as items from sections main where main.parentSectionId is null order by main.displayRank, main.text for json auto, include_null_values