cs_threads_getthreadset.prc

community server 源码

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [dbo].cs_Threads_GetThreadSet
(
	@SectionID int,
	@PageIndex int, 
	@PageSize int,
	@sqlCount ntext,
	@sqlPopulate ntext,
	@UserID int,
	@SettingsID int,
	@ReturnRecordCount bit,
	@IncludeCategories bit = 0
)
AS

SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

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

-- Set sticky posts unsticky by limiting the scope of the query: the ideea is any user will clean up its stickies when it gets the chance
IF (@SectionID IS NOT NULL AND @SectionID >= 0)
	UPDATE cs_Threads 
	SET StickyDate = ThreadDate, IsSticky = 0 
	WHERE SectionID = @SectionID AND UserID = @UserID AND IsSticky = 1 AND StickyDate < GETDATE() AND SettingsID = @SettingsID
ELSE
	UPDATE cs_Threads 
	SET StickyDate = ThreadDate, IsSticky = 0 
	WHERE UserID = @UserID AND IsSticky = 1 AND StickyDate < GETDATE() AND SettingsID = @SettingsID

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

-- 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,
	ThreadID int
)

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

SELECT
	jT.*, U.*,
	HasRead = 0,
	jP.PostID,
	jP.Subject,
	jP.Body,
	jP.FormattedBody,
	UserName = jT.PostAuthor,
	jP.PropertyNames as PostPropertyNames, jp.PropertyValues as PostPropertyValues,
	jP.PostConfiguration
FROM 
	#PageIndex jPI
	JOIN cs_Threads jT ON jPI.ThreadID = jT.ThreadID
	JOIN cs_Posts jP ON jPI.ThreadID = jP.ThreadID
	JOIN cs_vw_Users_FullUser U ON jP.UserID = U.cs_UserID and U.SettingsID = @SettingsID
WHERE 
	jPI.IndexID > @PageLowerBound
	AND jPI.IndexID < @PageUpperBound
	AND jP.PostLevel = 1 	-- PostLevel=1 should mean it's a top-level thread starter
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], jP.PostID
	FROM 
		#PageIndex jPI
		JOIN cs_Posts jP ON jPI.ThreadID = jP.ThreadID
		JOIN cs_Posts_InCategories PIC ON jP.PostID = PIC.PostID
		JOIN cs_Post_Categories Cats ON PIC.CategoryID = Cats.CategoryID
	WHERE 
		jPI.IndexID > @PageLowerBound
		AND jPI.IndexID < @PageUpperBound
		AND jP.PostLevel = 1 AND jP.SettingsID = @SettingsID
End

-- Update that the user has read this forum
IF @UserID > 0
BEGIN
	IF @SectionID > 0
		EXEC cs_Section_MarkRead @UserID, @SectionID, @SettingsID, @GroupID
END

SET ROWCOUNT @RowsToReturn

-- Do we need to return a record count?
-- *************************************
IF (@ReturnRecordCount = 1)
	EXEC (@sqlCount)

-- Return the users that the message is to if this
-- is a private message
IF @SectionID = 0
	SELECT 
		U.*,
		P2.ThreadID 
	FROM
		cs_PrivateMessages P1, 
		cs_PrivateMessages P2,
		cs_vw_Users_FullUser U
		
	WHERE 
		P1.UserID = @UserID AND 
		P2.UserID <> @UserID AND 
		P2.UserID = U.cs_UserID AND
		P1.ThreadID = P2.ThreadID and U.SettingsID = @SettingsID and P1.SettingsID = @SettingsID and P2.SettingsID = @SettingsID

END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_Threads_GetThreadSet]  TO [public]
GO