declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @partnerId int = ; declare @partnerType varchar(3) = ; with posts as ( select p.postId, p.subjectId, p.subjectType, p.postTitle, p.postComment, p.postDatetime, p.postedBy, p.posterFirstName, p.posterLastName, p.posterOrganization, p.posterOrgId, p.posterOrgType, p.revisedBy, p.revisedOn, p.revisedReason, p.revisorFirstName, p.revisorLastName, p.restrictedComment, p.postType , rep.FileID, rep.OwnerID, rep.OwnerType, rep.ContainerID, rep.ContainerType, rep.DocTitle, rep.Category, rep.UploadDate, rep.ContentID, rep.FileSize, rtrim( rep.FileExt ) fileExt, rep.DocDescription, rep.SortRank, rep.restricted, fsc.FileName, REPLACE( path.docPath, 'http://doclibrary.com', 'https://dev.fsenablers.com/FSLContent') path.docPath as docPath, fsc.mainFolder, fsc.subfolder, fsc.subfolder2, coalesce( cat.sortRank, 1000 ) catSortRank, cat.categoryDescription from tbl_WEBLOG_Posts p left outer join tbl_WEBLOG_Files f with (nolock) on f.postId = p.postId and f.ownerType = p.ownerType and f.ownerId = p.ownerId left outer join tbl_OPR_Repository rep on rep.fileId = f.fileId and rep.ownerId = f.ownerId and rep.ownerType = f.ownerType left outer join tbl_FSC_Content fsc on fsc.contentID = rep.contentID left outer join qryDocFullPathLookup path on path.contentID = rep.contentID left outer join tbl_OPR_RepositoryCategories cat on cat.categoryId = rep.category and cat.ownerId = rep.ownerId and cat.ownerType = rep.ownerType and cat.containerType = rep.containerType where p.ownerId = @ownerId and p.ownerType = @ownerType and p.subjectId = @partnerId and p.subjectType = @partnerType and p.postId = and p.postType = and p.revisedDeleted = 'N' and p.restrictedComment = 'N' ) #forJSON( 'posts', attributes.datastore, 'postDatetime' )#