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
@ForumID	int	4	INPUT
@ViewType	int	4	INPUT
@PagesBack	int	4	INPUT

Total: 3 parameter(s)

SQL

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE      PROCEDURE forums_GetAllMessages
(
	@ForumID int,
	@ViewType int,
	@PagesBack int
)
 AS
-- The returned Recordset depends on the viewType option chosen
--	0 == Flat display
--	1 == Mixed display (just top-level posts)
--	2 == Threaded display
DECLARE @DaysToView int, @StartDate datetime, @StopDate datetime
SELECT @DaysToView = DaysToView FROM Forums (nolock) WHERE ForumID = @ForumID
SELECT @StartDate = DateAdd(dd, -@PagesBack * @DaysToView, getdate()) 
SELECT @StopDate = DateAdd(dd, -@DaysToView, @StartDate)
IF @ViewType = 0
	-- FLAT DISPLAY
	SELECT 
		Subject,
		PostID,
		ForumID = @ForumID,
		ThreadID,
		ParentID,
		PostLevel,
		SortOrder,
		Approved,
		PostDate,
		ThreadDate,	
		UserName,
		Replies = (SELECT COUNT(*) FROM Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
		Body,
		TotalMessagesInThread = 0, -- not used
		TotalViews,
		IsLocked
	FROM Posts P (nolock)
	WHERE Approved = 1 AND ForumID = @ForumID AND PostDate >= @StopDate AND PostDate <= @StartDate
	ORDER BY PostDate DESC
ELSE IF @ViewType = 1
	-- MIXED DISPLAY
	SELECT 
		Subject,
		PostID,
		ThreadID,
		ParentID,
		ForumID = @ForumID,
		PostLevel,
		SortOrder,
		PostDate = (SELECT Max(PostDate) FROM Posts WHERE P.ThreadID = ThreadID),
		Approved,
		ThreadDate,
		UserName,
		Replies = (SELECT COUNT(*) FROM Posts WHERE P.ThreadID = ThreadID AND PostLevel != 1),
		Body,
		TotalMessagesInThread = 0, -- not used
		TotalViews,
		IsLocked
	FROM Posts P (nolock)
	WHERE Approved = 1 AND ForumID = @ForumID AND PostLevel = 1 AND ThreadDate >= @StopDate AND ThreadDate <= @StartDate
	ORDER BY PostDate DESC
	
ELSE IF @ViewType = 2
	-- THREADED DISPLAY
	SELECT 
		Subject,
		PostID,
		ThreadID,
		ParentID,
		ForumID = @ForumID,
		PostLevel,
		SortOrder,
		Approved,
		PostDate,
		ThreadDate,
		UserName,
		Replies = 0,
		Body,
		TotalMessagesInThread = 0, -- not used
		TotalViews,
		IsLocked
	FROM Posts P (nolock)
	WHERE Approved = 1 AND ForumID = @ForumID AND ThreadDate >= @StopDate AND ThreadDate <= @StartDate
	ORDER BY ThreadID DESC, SortOrder

















GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO