cs_weblog_updateweblogrecentcontent_job.prc

解压即可使用

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_weblog_UpdateWeblogRecentContent_Job]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_weblog_UpdateWeblogRecentContent_Job]
GO



Create Proc [dbo].cs_weblog_UpdateWeblogRecentContent_Job
(
	@SettingsID int
) 

as

SET Transaction Isolation Level Read UNCOMMITTED

Set NOCount On

Update cs_weblog_weblogs
Set 
	MostRecentPostID = P.PostID , 
	MostRecentPostDate = B.BloggerTime, 
	MostRecentPostName = B.PostName, 
	MostRecentPostAuthorID = P.UserID, 
	MostRecentPostAuthor = P.PostAuthor, 
	MostRecentPostSubject = P.Subject

FROM cs_Posts p, cs_Weblog_Posts b, (Select Max(p.PostID) as PostID, p.SectionID FROM cs_Posts p, cs_Weblog_Posts b where  p.PostID = b.PostID and b.BlogPostType = 1 and b.BloggerTime <= getdate() and p.SettingsID = @SettingsID Group by p.SectionID) sub
where  p.PostID = b.PostID and p.PostID = sub.PostID and cs_weblog_weblogs.SectionID = sub.SectionID and cs_weblog_weblogs.SettingsID = @SettingsID



--We may be able to can the articles part


Update cs_weblog_weblogs
Set 
	MostRecentArticleID = P.PostID , 
	MostRecentArticleDate = B.BloggerTime, 
	MostRecentArticleName = B.PostName, 
	MostRecentArticleAuthorID = P.UserID, 
	MostRecentArticleAuthor = P.PostAuthor, 
	MostRecentArticleSubject = P.Subject

FROM cs_Posts p, cs_Weblog_Posts b, (Select Max(p.PostID) as PostID, p.SectionID FROM cs_Posts p, cs_Weblog_Posts b where  p.PostID = b.PostID and b.BlogPostType = 2 and b.BloggerTime <= getdate() and p.SettingsID = @SettingsID Group by p.SectionID) sub
where  p.PostID = b.PostID and p.PostID = sub.PostID and cs_weblog_weblogs.SectionID = sub.SectionID and cs_weblog_weblogs.SettingsID = @SettingsID



--Update Count Items
update cs_Weblog_Weblogs
Set
	PostCount = (Select Count(*) FROM cs_weblog_Posts w, cs_Posts p where w.PostID = p.PostID and w.BlogPostType = 1 and p.IsApproved = 1 and p.SectionID =cs_Weblog_Weblogs.SectionID),
	ArticleCount = (Select Count(*) FROM cs_weblog_Posts w, cs_Posts p where w.PostID = p.PostID and w.BlogPostType = 2 and p.IsApproved = 1 and p.SectionID =cs_Weblog_Weblogs.SectionID),
	CommentCount = (Select Count(*) FROM cs_weblog_Posts w, cs_Posts p where w.PostID = p.PostID and w.BlogPostType = 4 and p.IsApproved = 1 and p.SectionID =cs_Weblog_Weblogs.SectionID),
	TrackBackCount = (Select Count(*) FROM cs_weblog_Posts w, cs_Posts p where w.PostID = p.PostID and w.BlogPostType = 8 and p.IsApproved = 1 and p.SectionID =cs_Weblog_Weblogs.SectionID)
where cs_Weblog_Weblogs.SettingsID = @SettingsID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_weblog_UpdateWeblogRecentContent_Job] to public
go