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_BackUp ( @PostID int, @PageIndex int, @PageSize int, @SortBy int, @SortOrder bit, @UserName nvarchar(50) ) AS BEGIN DECLARE @PageLowerBound int DECLARE @PageUpperBound int -- 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 = (SELECT ThreadID FROM Posts (nolock) WHERE PostID = @PostID) AND PostID >= @PostID AND ParentID >= (SELECT ParentID FROM Posts (nolock) WHERE PostID = @PostID) AND SortOrder >= (SELECT SortOrder FROM Posts (nolock) WHERE PostID = @PostID) 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 = (SELECT ThreadID FROM Posts (nolock) WHERE PostID = @PostID) AND PostID >= @PostID AND ParentID >= (SELECT ParentID FROM Posts (nolock) WHERE PostID = @PostID) AND SortOrder >= (SELECT SortOrder FROM Posts (nolock) WHERE PostID = @PostID) 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 = (SELECT ThreadID FROM Posts (nolock) WHERE PostID = @PostID) AND PostID >= @PostID AND ParentID >= (SELECT ParentID FROM Posts (nolock) WHERE PostID = @PostID) AND SortOrder >= (SELECT SortOrder FROM Posts (nolock) WHERE PostID = @PostID) 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 = (SELECT ThreadID FROM Posts (nolock) WHERE PostID = @PostID) AND PostID >= @PostID AND ParentID >= (SELECT ParentID FROM Posts (nolock) WHERE PostID = @PostID) AND SortOrder >= (SELECT SortOrder FROM Posts (nolock) WHERE PostID = @PostID) ORDER BY Username DESC -- get the thread info for this post IF @UserName IS NOT NULL 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 FROM Posts P (nolock), #PageIndex WHERE P.PostID = #PageIndex.PostID AND #PageIndex.IndexID > @PageLowerBound AND #PageIndex.IndexID < @PageUpperBound 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 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 FROM Posts P (nolock), #PageIndex WHERE P.PostID = #PageIndex.PostID AND #PageIndex.IndexID > @PageLowerBound AND #PageIndex.IndexID < @PageUpperBound AND ForumID NOT IN (SELECT ForumID from PrivateForums) END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO