cs_shared_threads_getthreadset.prc

community server 源码

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_shared_Threads_GetThreadSet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_shared_Threads_GetThreadSet]
GO

CREATE  PROCEDURE [dbo].[cs_shared_Threads_GetThreadSet]
(
	@SectionID int,
	@PageIndex int, 
	@PageSize int,
	@sqlPopulate ntext,
	@UserID int,
	@IncludeCategories bit,
	@IncludePageIndex bit,
	@SettingsID int,
	@TotalRecords int output
)
AS
SET Transaction Isolation Level Read UNCOMMITTED

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
DECLARE @TotalThreads int

-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)


-- 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
)
 

--CREATE INDEX page_index ON #PageIndex(IndexID)

INSERT INTO #PageIndex (PostID)
EXEC (@sqlPopulate)

SET @TotalRecords = @@rowcount

SET ROWCOUNT @RowsToReturn

SELECT 
	SortOrder = jPI.IndexID,
	jP.PostID, 
	jP.ThreadID, 
	jP.ParentID, 
	jP.PostAuthor, 
	jP.UserID, 
	jP.SectionID, 
	jP.PostLevel, 
	jP.SortOrder, 
	jP.Subject, 
	jP.PostDate, 
	jP.IsApproved,
	jP.IsLocked as IsLocked,
	jP.IsIndexed, 
	jP.TotalViews as PostTotalViews,  --Conflicts with the Threads table which has precidence
	jP.Body, 
	jP.FormattedBody, 
	jP.IPAddress, 
	jP.PostType, 
	jP.PostMedia, 
	jP.EmoticonID, 
	jP.SettingsID, 
	jP.AggViews,
	jP.PostPropertyNames, 
	jP.PostPropertyValues,
	jP.PostConfiguration,
	jP.Points AS PostPoints,
	jp.AttachmentFilename,jp.ContentType, jp.IsRemote, jp.FriendlyFileName, jp.ContentSize, jp.[FileName],jp.Created, jP.Height, jP.Width,
	jP.PostName, 
	jP.ApplicationPostType, 
	jP.UserTime, 
	HasRead = 1,
	EditNotes = null, --(SELECT EditNotes FROM cs_PostEditNotes WHERE PostID = P.PostID),

	jT.PostAuthor as UserName,
	jT.TotalReplies as Replies, -- (SELECT COUNT(P2.PostID) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = jP.PostID AND P2.PostLevel != 1)

        jT.UserID as ThreadUserID,
        jT.PostAuthor as ThreadPostAuthor,
        jT.PostDate as ThreadPostDate,
        jT.ThreadDate,
        jT.LastViewedDate,
        jT.StickyDate,
        jT.TotalViews as TotalViews,
        jT.TotalReplies,
        jT.MostRecentPostAuthorID,
        jT.MostRecentPostAuthor,
        jT.MostRecentPostID,
        jT.IsLocked as ThreadIsLocked,
        jT.IsSticky,
        jT.IsApproved as ThreadIsApproved,
        jT.RatingSum,
        jT.TotalRatings,
        jT.ThreadEmoticonID,
        jT.ThreadStatus,
        jT.SettingsID
	, jP.PostStatus, jP.SpamScore
FROM 
	#PageIndex jPI
	JOIN cs_vw_PostsWithAttachmentDetails jP ON jPI.PostID = jP.PostID
	JOIN cs_Threads jT ON jP.ThreadID = jT.ThreadID
WHERE 
	jPI.IndexID > @PageLowerBound
	AND jPI.IndexID < @PageUpperBound
	--AND jP.PostLevel = 1 	-- PostLevel=1 should mean it's a top-level thread starter,
	--AND jp.SettingsID = @SettingsID AND jT.SettingsID = @SettingsID
ORDER BY
	jPI.IndexID	-- this is the ordering system we're using populated from the @sqlPopulate


SET ROWCOUNT 0

IF @IncludeCategories = 1
BEGIN

	SELECT 
		Cats.[Name], jPI.PostID
	FROM 
		#PageIndex jPI
		JOIN cs_Posts_InCategories PIC ON jPI.PostID = PIC.PostID
		JOIN cs_Post_Categories Cats ON PIC.CategoryID = Cats.CategoryID
	WHERE 
		jPI.IndexID > @PageLowerBound
		AND jPI.IndexID < @PageUpperBound

End

If @IncludePageIndex = 1
BEGIN
	SELECT IndexID, PostID from #PageIndex ORDER BY IndexID
END

DROP TABLE #PageIndex

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_shared_Threads_GetThreadSet] to public
go