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_GetSingleMessage
(
	@PostID int,
	@UserName nvarchar(50)
) AS
DECLARE @NextThreadID int, @PrevThreadID int, @NextPostID int, @PrevPostID int, @ThreadID int, @ForumID int, @SortOrder int
SELECT @ThreadID = ThreadID, @ForumID = ForumID, @SortOrder=SortOrder
FROM Posts (nolock) WHERE PostID = @PostID
exec dbo.forums_GetNextThreadID @ThreadID, @ForumID, @NextThreadID OUTPUT
exec dbo.forums_GetPrevThreadID @ThreadID, @ForumID, @PrevThreadID OUTPUT
exec dbo.forums_GetNextPostID @ThreadID, @SortOrder, @ForumID, @NextPostID OUTPUT
exec dbo.forums_GetPrevPostID @ThreadID, @SortOrder, @ForumID, @PrevPostID OUTPUT
DECLARE @TrackingThread bit
IF EXISTS(SELECT ThreadID FROM ThreadTrackings (nolock) WHERE ThreadID = @ThreadID AND UserName=@UserName)
	SELECT @TrackingThread = 1
ELSE
	SELECT @TrackingThread = 0

IF @UserName IS NOT NULL
	SELECT
		Subject,
		ForumID = @ForumID,
		ForumName = (SELECT Name FROM Forums WHERE ForumID = @ForumID),
		ThreadID = @ThreadID,
		ParentID,
		PostLevel,
		SortOrder = @SortOrder,
		PostDate,
		ThreadDate,
		P.UserName,
		U.FakeEmail,
		U.URL,
		U.Signature,
		P.Approved,
		Replies = (SELECT COUNT(*) FROM Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
		PrevThreadID = @PrevThreadID,
		NextThreadID = @NextThreadID,
		PrevPostID = @PrevPostID,
		NextPostID = @NextPostID,
		UserIsTrackingThread = @TrackingThread,
		Body,
		IsLocked
	FROM 
		Posts P (nolock)
	INNER 
		JOIN Users U (nolock) ON
		U.UserName = P.UserName
	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,
		ForumID = @ForumID,
		ForumName = (SELECT Name FROM Forums WHERE ForumID = @ForumID),
		ThreadID = @ThreadID,
		ParentID,
		PostLevel,
		SortOrder = @SortOrder,
		PostDate,
		ThreadDate,
		P.UserName,
		U.FakeEmail,
		U.URL,
		U.Signature,
		P.Approved,
		Replies = (SELECT COUNT(*) FROM Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
		PrevThreadID = @PrevThreadID,
		NextThreadID = @NextThreadID,
		PrevPostID = @PrevPostID,
		NextPostID = @NextPostID,
		UserIsTrackingThread = @TrackingThread,
		Body,
		IsLocked
	FROM 
		Posts P (nolock)
	INNER 
		JOIN Users U (nolock) ON
		U.UserName = P.UserName
	WHERE 
		P.PostID = @PostID AND
		ForumID NOT IN (SELECT ForumID from PrivateForums)









GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO