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
@UserName	nvarchar	100	INPUT

Total: 2 parameter(s)

SQL

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE           PROCEDURE forums_GetThreadByPostID
(
	@PostID	int,
	@UserName nvarchar(50)
)
 AS
BEGIN

DECLARE @ThreadID int

	-- Get the thread id of the post
	SELECT @ThreadID = ThreadID FROM Posts WHERE PostID = @PostID

	-- get the thread info for this post
	IF @UserName IS NOT NULL
		SELECT
			PostID,
			ThreadID,
			ForumID,
			ForumName = (SELECT Name FROM Forums F (nolock) WHERE F.ForumID = P.ForumID),
			Subject,
			ParentID,
			PostLevel,
			SortOrder,
			PostDate,
			ThreadDate,
			UserName,
			Approved,
			Replies = (SELECT COUNT(*) FROM Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
			Body,
			TotalViews,
			IsLocked,
			HasRead = 0 -- not used
		FROM 
			Posts P (nolock)
		WHERE 
			Approved=1 AND 
			ThreadID = @ThreadID AND
			SortOrder >= (SELECT SortOrder FROM Posts (nolock) WHERE PostID = @ThreadID) 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)))
		ORDER BY 
			SortOrder
	ELSE
		SELECT
			PostID,
			ThreadID,
			ForumID,
			ForumName = (SELECT Name FROM Forums F (nolock) WHERE F.ForumID = P.ForumID),
			Subject,
			ParentID,
			PostLevel,
			SortOrder,
			PostDate,
			ThreadDate,
			UserName,
			Approved,
			Replies = (SELECT COUNT(*) FROM Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
			Body,
			TotalViews,
			IsLocked,
			HasRead = 0 -- not used
		FROM 
			Posts P (nolock)
		WHERE 
			Approved=1 AND 
			ThreadID = @ThreadID AND
			SortOrder >= (SELECT SortOrder FROM Posts (nolock) WHERE PostID = @ThreadID) AND
			ForumID NOT IN (SELECT ForumID from PrivateForums)
		ORDER BY 
			SortOrder
END























GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO