May 26, 2017 at 10:05 am
Below is the stored procedure which is supposed to return News Article data based on the parameter criteria provided. One of the filters is that the Article’s Status must be “Published”. I’ve been told that the stored procedure has returned articles with the Status of “Archived”. Can someone please look at the code and tell me? I don't have a way to run this SP because a developer gave me this code to look at and I can't run it because I don't have permission on the system. Any insight is appreciated,
C
-- All parameters are optional
@CategoryID as varchar(800) = null,
@KeywordID as varchar(2000) = null,
@BrandID as varchar(800) = null,
@Date as varchar(50) = null,
@IsBreaking as Bit = null,
@IsSample as Bit = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @IsSample = 0
BEGIN
PRINT 'Real Data';
IF @Date IS NULL SET @Date = DATEADD(day, -1, GETDATE());
SELECT DISTINCT art.ArticleId,
art.Headline,
art.PageTitle,
art.PostDate,
art.Summary,
art.Body,
art.[Status],
art.MetaKeywords,
art.MetaDescription,
art.MainImageCaption,
art.MainImageCredit,
art.ThumbnailCaption,
art.EmbeddedVideoId,
art.EmbeddedGalleryId,
art.ShortHeadline,
dbo.uf_GetDomainByArticle( art.ArticleId )
+ art.CustomUrl as QualifiedArticleURL,
dbo.uf_GetDomainByArticle( art.ArticleId ) + '/assets/'
+ CONVERT(VARCHAR(2), a.SiteId)
+ Replace(a.ToolSectionURL, '\', '/')
+a.[Filename] AS QualifiedImageURL,
a.AlternateText AS ImageAlternateText,
aut.NAME AS AuthorName,
aut.Title AS AuthorTitle,
b.NAME AS AuthorBrand,
Case
When art.BrightCoveMediaID Is Not Null Then
'http://players.brightcove.net/5176256085001/default_default/index.html?videoId='
+ art.BrightCoveMediaID
When art.MediaID Is Not Null Then
'http://link.videoplatform.limelight.com/media/?mediaId='
+ llm.MediaID + '&channelId=' + llm.ChannelID
+ '&playerForm=2b84c3101f624f1d9f5857abfc109d52&deepLink=true'
Else Null
End AS QualifiedVideoURL,
b.Name as ArticleBrand,
dbo.uf_GetKeywordsIDsByArticle(art.ArticleId) ASArticleKeywords
FROM NewsArticle art
LEFT JOIN NewsAuthor aut
ON art.AuthorId = aut.AuthorId
LEFT JOIN Asset a
ON art.MainImageAssetFirstId = a.FirstId
LEFT JOIN FJM_LimelightMedia llm
ON art.MediaID = llm.MediaID
LEFT JOIN Brand b
ON aut.BrandId = b.BrandId
LEFT JOIN NewsArticleKeyword nak
ON art.ArticleId = nak.ArticleId
LEFT JOIN Keyword k
ON nak.KeywordId = k.KeywordId
WHERE art.DisplayMode = 'H'
AND art.[Status] = 'Published'
AND ( @KeywordID IS NULL
OR nak.KeywordId IN ( SELECT Item FROM uf_DelimitedSplit8K ( @KeywordID, ',' )) )
AND art.PostDate BETWEEN @Date AND GETDATE()
AND ( @IsBreaking IS NULL
OR art.IsSpotlight = @IsBreaking )
AND art.SiteId = 1
ORDER BY art.PostDate DESC
END
ELSE
BEGIN
PRINT 'Sample Data';
IF @Date IS NULL SET @Date = DATEADD(year, -1, GETDATE());
SELECT DISTINCT TOP 10 art.ArticleId,
art.Headline,
art.PageTitle,
art.PostDate,
art.Summary,
art.Body,
art.[Status],
art.MetaKeywords,
art.MetaDescription,
art.MainImageCaption,
art.MainImageCredit,
art.ThumbnailCaption,
art.EmbeddedVideoId,
art.EmbeddedGalleryId,
art.ShortHeadline,
'http://www.agweb.com'
+ art.CustomUrl as QualifiedArticleURL,
'http://www.agweb.com/assets/'
+ CONVERT(VARCHAR(2), a.SiteId)
+ Replace(a.ToolSectionURL, '\', '/')
+a.[Filename] AS QualifiedImageURL,
a.AlternateText AS ImageAlternateText,
aut.NAME AS AuthorName,
aut.Title AS AuthorTitle,
b.NAME ASAuthorBrand,
Case
When art.BrightCoveMediaID Is Not Null Then
'http://players.brightcove.net/5176256085001/default_default/index.html?videoId='
+ art.BrightCoveMediaID
When art.MediaID Is Not Null Then
'http://link.videoplatform.limelight.com/media/?mediaId='
+ llm.MediaID + '&channelId=' + llm.ChannelID
+ '&playerForm=2b84c3101f624f1d9f5857abfc109d52&deepLink=true'
Else Null
End AS QualifiedVideoURL,
b.Name as ArticleBrand,
dbo.uf_GetKeywordsIDsByArticle(art.ArticleId) ASArticleKeywords
FROM NewsArticle art
LEFT JOIN NewsAuthor aut
ON art.AuthorId = aut.AuthorId
LEFT JOIN Asset a
ON art.MainImageAssetFirstId = a.FirstId
LEFT JOIN FJM_LimelightMedia llm
ON art.MediaID = llm.MediaID
LEFT JOIN Brand b
ON aut.BrandId = b.BrandId
LEFT JOIN NewsArticleKeyword nak
ON art.ArticleId = nak.ArticleId
LEFT JOIN Keyword k
ON nak.KeywordId = k.KeywordId
WHERE art.DisplayMode = 'H'
AND art.[Status] = 'Published'
AND ( @KeywordID IS NULL
OR nak.KeywordId IN ( SELECT Item FROM uf_DelimitedSplit8K ( @KeywordID, ',' )) )
AND art.PostDate BETWEEN DATEADD(year, -1, @Date) AND@Date
AND ( @IsBreaking IS NULL
ORart.IsSpotlight = @IsBreaking )
AND art.SiteId = 1
ORDER BY art.PostDate DESC
END
END
May 26, 2017 at 1:49 pm
You have two queries here and both have "AND art.[Status] = 'Published'" hard coded into the WHERE clause. AND... That is the same status being referenced in both SELECT lists.
So, the next question is this... Are the complainers seeing the word "Archived " in the output? OR are they simply seeing articles that they think should be in an Archived status?
As a betting man, my money is on the later...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply