Stored Procedure Icon dbo.Statistics_UpdateForumStatistics

Stored Procedure Properties

Name Value
Owner dbo
Created 2004-05-31
Startup False
Encrypted False

Creation Options

Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Parameters

Name DataType Length Type
@ForumID int 4 INPUT
@ThreadID int 4 INPUT
@PostID int 4 INPUT

Total: 3 parameter(s)

SQL

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



--exec Statistics_UpdateForumStatistics 15, 8139, 8139

CREATE  procedure Statistics_UpdateForumStatistics
(
	@ForumID int,
	@ThreadID int,
	@PostID int
)
AS
BEGIN
DECLARE @Username nvarchar(50)
DECLARE @PostDate datetime
DECLARE @TotalPosts int
DECLARE @TotalThreads int

-- Get values necessary to update the forum statistics
SELECT
	@Username = username,
	@PostDate = PostDate,
	@TotalPosts = (SELECT COUNT(*) FROM Posts P2 (nolock) WHERE P2.ForumID = P.ForumID AND P2.Approved=1),
	@TotalThreads = (SELECT COUNT(*) FROM Posts P2 (nolock) WHERE P2.ForumID = P.ForumID AND P2.Approved=1 AND P2.PostLevel=1)
FROM
	Posts P
WHERE
	PostID = @PostID

-- Do the update within a transaction
BEGIN TRAN

	UPDATE 
		Forums
	SET
		TotalPosts = @TotalPosts,
		TotalThreads = @TotalThreads,
		MostRecentPostID = @PostID,
		MostRecentThreadID = @ThreadID,
		MostRecentPostDate = @PostDate,
		MostRecentPostAuthor = @Username
	WHERE
		ForumID = @ForumID

COMMIT TRAN

END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


					

Generated on 26/08/2004 15:05:35 by DataAide.