Stored Procedure Properties

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

Creation Options

Name	Value
QUOTED_IDENTIFIER	OFF
ANSI_NULLS	OFF

Parameters

Name	DataType	Length	Type
@PostID	int	4	INPUT
@TrackViews	bit	1	INPUT
@UserName	nvarchar	100	INPUT

Total: 3 parameter(s)

SQL

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE        PROCEDURE forums_GetPostInfo
(
	@PostID	int,
	@TrackViews bit,
	@UserName nvarchar (50)
)
 AS
BEGIN

	IF @TrackViews = 1
	BEGIN
		DECLARE @views int
	
		-- Update the counter for the number of times this post is viewed
		SELECT @views = TotalViews FROM Posts WHERE PostID = @PostID
		UPDATE Posts SET TotalViews = (@views + 1) WHERE PostID = @PostID
	END

	-- If @UserName is null it is an anonymous user
	IF @UserName IS NOT NULL
	BEGIN
		DECLARE @ForumID int
		DECLARE @PostDate datetime

		-- Mark the post as read
		-- *********************

		-- Only for PostLevel = 1
		IF EXISTS (SELECT PostID FROM Posts WHERE PostID = @PostID AND PostLevel = 1)
			IF NOT EXISTS (SELECT HasRead FROM PostsRead WHERE Username = @UserName and PostID = @PostID)
				INSERT INTO PostsRead (Username, PostID) VALUES (@UserName, @PostID)

	END

	IF @UserName IS NOT NULL
		SELECT
			Subject,
			PostID,
			UserName,
			P.ForumID,
			ForumName = (SELECT Name FROM Forums F (nolock) WHERE F.ForumID = P.ForumID),
			ParentID,
			ThreadID,
			Approved,
			PostDate,
			PostLevel,
			SortOrder,
			ThreadDate,
			Replies = (SELECT COUNT(*) FROM Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
			Body,
			TotalMessagesInThread = 0, -- not used
			TotalViews,
			IsLocked,
			HasRead = 1
		FROM 
			Posts P (nolock)
		WHERE 
			P.PostID = @PostID AND
			(ForumID NOT IN (SELECT ForumID from PrivateForums) OR
			ForumID IN (SELECT ForumID FROM PrivateForums WHERE RoleName IN (SELECT RoleName from UsersInRoles WHERE username = @UserName)))
	ELSE
		SELECT
			Subject,
			PostID,
			UserName,
			P.ForumID,
			ForumName = (SELECT Name FROM Forums F (nolock) WHERE F.ForumID = P.ForumID),
			ParentID,
			ThreadID,
			Approved,
			PostDate,
			PostLevel,
			SortOrder,
			ThreadDate,
			Replies = (SELECT COUNT(*) FROM Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
			Body,
			TotalMessagesInThread = 0, -- not used
			TotalViews,
			IsLocked,
			HasRead = 1
		FROM 
			Posts P (nolock)
		WHERE 
			P.PostID = @PostID AND
			ForumID NOT IN (SELECT ForumID from PrivateForums)


END


















GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO