cs_system_updateforum.prc

解压即可使用

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE        procedure [dbo].cs_system_UpdateForum
(
	@SectionID int,
	@ThreadID int,
	@PostID int,
	@SettingsID int
)
AS
BEGIN
DECLARE @UserID 		int
DECLARE @PostDate 		datetime
DECLARE @TotalThreads 		int
DECLARE @TotalPostsApproved	int
DECLARE @TotalPosts 		int
DECLARE @Subject		nvarchar(64)
DECLARE @User 		nvarchar(64)
DECLARE @PostConfiguration	int

-- Set default
SET @PostConfiguration = 0

-- Get values necessary to update the forum statistics
SELECT
	@UserID = P.UserID,
	@PostDate = PostDate,
	@TotalPostsApproved = (SELECT COUNT(P2.PostID) FROM cs_Posts P2 (nolock) WHERE P2.SectionID = @SectionID AND P2.IsApproved=1 and P2.SettingsID = @SettingsID),
	@TotalPosts = (SELECT COUNT(PostID) FROM cs_Posts WHERE SectionID = @SectionID and SettingsID = @SettingsID),
	@TotalThreads = (SELECT COUNT(P2.PostID) FROM cs_Posts P2 (nolock) WHERE P2.SectionID = @SectionID AND P2.IsApproved=1 AND P2.PostLevel=1 and P2.SettingsID = @SettingsID),
	@Subject = P.Subject,
	@User = PostAuthor,
	@PostConfiguration = PostConfiguration
FROM
	cs_Posts P
WHERE
	PostID = @PostID and P.SettingsID = @SettingsID and P.SectionID = @SectionID

-- Is anonymous post?
IF (@PostConfiguration & 1) = 1
BEGIN
	-- Set anonymous info instead real user info
	SELECT @UserID = cs_UserID FROM cs_vw_Users_FullUser WHERE SettingsID = @SettingsID and IsAnonymous = 1	
	SET @User = ''
END

-- Do the update within a transaction
BEGIN TRAN

	UPDATE 
		cs_Sections
	SET
		TotalPosts = isnull(@TotalPosts,0),
		TotalThreads = isnull(@TotalThreads,0),
		MostRecentPostID = isnull(@PostID,0),
		MostRecentThreadID = isnull(@ThreadID,0),
		MostRecentPostDate = isnull(@PostDate,'1/01/1797'),
		MostRecentPostAuthorID = isnull(@UserID,0),
		MostRecentPostSubject = isnull(@Subject,''),
		MostRecentPostAuthor = isnull(@User,''),
		MostRecentThreadReplies = ISNULL((SELECT TotalReplies FROM cs_Threads WHERE ThreadID = @ThreadID), 0)
	WHERE
		SectionID = @SectionID and SettingsID = @SettingsID

COMMIT TRAN

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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