Stored Procedure Properties

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

Creation Options

Name	Value
QUOTED_IDENTIFIER	OFF
ANSI_NULLS	ON

Parameters

Name	DataType	Length	Type
@PostID	int	4	INPUT
@PageIndex	int	4	INPUT
@PageSize	int	4	INPUT
@SortBy	int	4	INPUT
@SortOrder	bit	1	INPUT
@UserName	nvarchar	100	INPUT

Total: 6 parameter(s)

SQL

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



CREATE              PROCEDURE forums_GetThreadByPostIDPaged
(
	@PostID	int,
	@PageIndex int,
	@PageSize int,
	@SortBy int,
	@SortOrder bit,
	@UserName nvarchar(50)
)
 AS
BEGIN

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @ThreadID int
DECLARE @ForumID int
DECLARE @PrivateForumID int
DECLARE @IsPrivateForum bit
DECLARE @IsUserApprovedForPrivateForum bit

-- Get the ForumID, the PrivateForumID, and the ThreadID
SELECT @ForumID = ForumID, @ThreadID = ThreadID FROM Posts WHERE PostID = @PostID
SELECT @PrivateForumID = ForumID FROM PrivateForums WHERE ForumID = @ForumID

-- Is this a private forum?
IF @PrivateForumID IS NOT NULL
  BEGIN
    -- This is a private forum
    SET @IsPrivateForum = 1

    -- Does the user have access to this forum?
    IF (@Username IS NOT NULL)
      BEGIN
        IF EXISTS(SELECT ForumID FROM PrivateForums WHERE RoleName IN (SELECT RoleName from UsersInRoles WHERE username = @UserName))
          SET @IsUserApprovedForPrivateForum = 1
        ELSE
          SET @IsUserApprovedForPrivateForum = 0
      END
    ELSE
      SET @IsUserApprovedForPrivateForum = 0
  END
ELSE
  SET @IsPrivateForum = 0

-- Let's return here if the user is not allowed
IF @IsPrivateForum = 1 AND @IsUserApprovedForPrivateForum = 0
  RETURN

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- Create a temp table to store the select results
CREATE TABLE #PageIndex 
(
	IndexId int IDENTITY (1, 1) NOT NULL,
	PostID int
)

-- Sort by Post Date
IF @SortBy = 0 AND @SortOrder = 0
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM Posts P (nolock) WHERE Approved = 1 AND ThreadID = @ThreadID ORDER BY PostDate

ELSE IF @SortBy = 0 AND @SortOrder = 1
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM Posts P (nolock) WHERE Approved = 1 AND ThreadID = @ThreadID ORDER BY PostDate DESC

-- Sort by Author
IF @SortBy = 1 AND @SortOrder = 0
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM Posts P (nolock) WHERE Approved = 1 AND ThreadID = @ThreadID ORDER BY Username
ELSE IF @SortBy = 1 AND @SortOrder = 1
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM Posts P (nolock) WHERE Approved = 1 AND ThreadID = @ThreadID ORDER BY Username DESC

SELECT
	P.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,
	TotalMessagesInThread = 0, -- not used
	HasRead = 0, -- not used
	PostType
FROM 
	Posts P (nolock),
	#PageIndex
WHERE 
	P.PostID = #PageIndex.PostID AND
	#PageIndex.IndexID > @PageLowerBound AND
	#PageIndex.IndexID < @PageUpperBound
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO