cs_weblog_updatecontenthistory.prc

community server 源码

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



/* TO ADD existing blog data
Insert cs_weblog_Weblogs (SectionID)
Select SectionID FROM cs_Sections where applicationtype = 1
*/

CREATE Proc [dbo].cs_weblog_UpdateContentHistory
(
	@SectionID int,
	@BlogPostTypeFilter tinyint = 0
)
as
SET Transaction Isolation Level Read UNCOMMITTED
If @BlogPostTypeFilter = 1
BEGIN

	Declare @MostRecentPostID int
	Declare @MostRecentPostDate datetime
	Declare @MostRecentPostName nvarchar(256)
	Declare @MostRecentPostAuthorID int
	Declare @MostRecentPostAuthorName nvarchar(256)
	Declare @MostRecentPostSubject nvarchar(256)
	Declare @PostCount int

	--Get Post Info
	Select Top 1 
		@MostRecentPostID = P.PostID, 
		@MostRecentPostDate =  P.PostDate, 
		@MostRecentPostAuthorID = UserID, 
		@MostRecentPostAuthorName = PostAuthor, 
		@MostRecentPostName = PostName,
		@MostRecentPostSubject = Subject 
	FROM cs_Posts P
	where P.ApplicationPostType = 1 and P.SectionID = @SectionID and P.IsApproved = 1 Order by P.PostDate Desc

	Select @PostCount = (Select Count(*) FROM cs_Posts P where P.SectionID = @SectionID and P.IsApproved = 1 and ApplicationPostType = 1)

	update cs_weblog_Weblogs
	Set
		MostRecentPostID = isnull(@MostRecentPostID,0),
		MostRecentPostDate = isnull(@MostRecentPostDate,getdate()),
		MostRecentPostAuthorID = isnull(@MostRecentPostAuthorID,0),
		MostRecentPostAuthor = isnull(@MostRecentPostAuthorName,''),
		MostRecentPostName = @MostRecentPostName,
		MostRecentPostSubject = isnull(@MostRecentPostSubject,''),
		PostCount = @PostCount
	Where SectionID = @SectionID
END
Else If @BlogPostTypeFilter = 2
BEGIN

	Declare @MostRecentArticleID int
	Declare @MostRecentArticleDate datetime
	Declare @MostRecentArticleName nvarchar(256)
	Declare @MostRecentArticleAuthorID int
	Declare @MostRecentArticleAuthorName nvarchar(256)
	Declare @MostRecentArticleSubject nvarchar(256)
	Declare @ArticleCount int

--Get Article Info
	Select Top 1 
		@MostRecentArticleID = P.PostID, 
		@MostRecentArticleDate = P.PostDate, 
		@MostRecentArticleAuthorID = UserID, 
		@MostRecentArticleAuthorName = PostAuthor, 
		@MostRecentArticleName = PostName,
		@MostRecentArticleSubject = Subject
	FROM cs_Posts P
	where P.ApplicationPostType = 2 and P.SectionID = @SectionID and P.IsApproved = 1 Order by P.PostDate Desc

	Select @ArticleCount = (Select Count(*) FROM cs_Posts P where P.SectionID = @SectionID and P.IsApproved = 1 and ApplicationPostType = 2)


	update cs_weblog_Weblogs
	Set
		MostRecentArticleID = isnull(@MostRecentArticleID,0),
		MostRecentArticleDate = isnull(@MostRecentArticleDate,getdate()),
		MostRecentArticleAuthorID = isnull(@MostRecentArticleAuthorID,0),
		MostRecentArticleAuthor = isnull(@MostRecentArticleAuthorName,''),
		MostRecentArticleName = @MostRecentArticleName,
		MostRecentArticleSubject = isnull(@MostRecentArticleSubject,''),
		ArticleCount = @ArticleCount
	Where SectionID = @SectionID
End
ELSE If @BlogPostTypeFilter = 4
BEGIN
	Declare @CommentCount int
	Select @CommentCount = (Select Count(*) FROM cs_Posts P where P.SectionID = @SectionID and P.IsApproved = 1 and ApplicationPostType = 4)
	update cs_weblog_Weblogs
	Set
		CommentCount = @CommentCount
	Where SectionID = @SectionID
END
ELSE If @BlogPostTypeFilter = 8
BEGIN
	Declare @TrackBackCount int
	Select @TrackBackCount = (Select Count(*) FROM cs_Posts P where P.SectionID = @SectionID and P.IsApproved = 1 and ApplicationPostType = 8)
	update cs_weblog_Weblogs
	Set
		TrackbackCount = @TrackBackCount
	Where SectionID = @SectionID
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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