cs_posts_updatepostsincategories.prc

解压即可使用

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE  Proc [dbo].cs_Posts_UpdatePostsInCategories
(
	@CategoryList nvarchar(4000),
	@SectionID int,
	@PostID int,
	@CategoryType tinyint,
	@SettingsID int,
	@UpdateStats bit = 1
)
as

DELETE FROM cs_Posts_InCategories where PostID = @PostID

IF @CategoryList Is Null OR LEN(LTRIM(RTRIM(@CategoryList))) = 0
RETURN


DECLARE @idoc int
-- declare @CategoryList nvarchar(4000)
-- select @CategoryList = "<?xml version=""1.0"" ?><Categories><Category>Test</Category></Categories>"

EXEC sp_xml_preparedocument @idoc OUTPUT, @CategoryList


--Insert Missing Categories
INSERT INTO cs_Post_Categories (SectionID, [Name], CategoryType, IsEnabled, ParentID, [Description], SettingsID)
	Select 	
		@SectionID, C.[Category], @CategoryType, 1, 0, null, @SettingsID
	FROM 
		OPENXML(@idoc, '/Categories', 3)
			with (Category nvarchar(512) ) as C
	where 
		C.[Category] is not null  
		and C.[Category] not in (
			Select [Name] FROM cs_Post_Categories where  CategoryType = @CategoryType and SectionID = @SectionID
		) 

	--Insert Entry Categories
	INSERT INTO cs_Posts_InCategories 
		( PostID, CategoryID, SettingsID)
	Select 
		@PostID, C.CategoryID, @SettingsID
	FROM 
		OPENXML(@idoc, '/Categories/Category/', 2) X
		inner join cs_Post_Categories C on C.[Name] = Convert(nvarchar(256),X.[text]) collate database_default
			and C.CategoryType = @CategoryType and C.SectionID = @SectionID
	

EXEC sp_xml_removedocument @idoc

if(@UpdateStats = 1)
Begin

	-- Update the most recent post dates and total thread stuff for the categories
	declare @UpCategoryID int
	declare @UpPath nvarchar(256)
	DECLARE Categories_Cursor CURSOR FOR
		SELECT CategoryID FROM cs_Post_Categories WHERE SectionID = @SectionID
	
	OPEN Categories_Cursor
	FETCH NEXT FROM Categories_Cursor INTO @UpCategoryID
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
	
		set @UpPath = (select Path from cs_Post_Categories where CategoryID = @UpCategoryID)
	
		UPDATE cs_Post_Categories SET
			TotalThreads = (SELECT COUNT(PIC.PostID) FROM cs_Posts_InCategories PIC INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID) WHERE PIC.CategoryID = @UpCategoryID),
			TotalSubThreads = (SELECT COUNT(P.PostID) FROM cs_Posts P INNER JOIN cs_Post_Categories jC ON (jC.CategoryID = @UpCategoryID OR jC.Path LIKE @UpPath + convert(nvarchar, @UpCategoryID) + '/%') INNER JOIN cs_Posts_InCategories jPIC ON (jPIC.CategoryID = jC.CategoryID) WHERE P.PostID = P.ParentID AND P.PostID = jPIC.PostID),
			MostRecentPostDate = (SELECT MAX(PostDate) FROM cs_Posts_InCategories PIC INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID) WHERE PIC.CategoryID = @UpCategoryID),
			MostRecentSubPostDate = (SELECT MAX(PostDate) FROM cs_Posts P INNER JOIN cs_Post_Categories jC ON (jC.CategoryID = @UpCategoryID OR jC.Path LIKE @UpPath + convert(nvarchar, @UpCategoryID) + '/%') INNER JOIN cs_Posts_InCategories jPIC ON (jPIC.CategoryID = jC.CategoryID) WHERE P.PostID = P.ParentID AND P.PostID = jPIC.PostID)
		WHERE CategoryID = @UpCategoryID
	
		FETCH NEXT FROM Categories_Cursor INTO @UpCategoryID
	END
	
	CLOSE Categories_Cursor
	DEALLOCATE Categories_Cursor
	
END


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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