cs_weblog_threads_getthreadset.prc

解压即可使用

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE      procedure [dbo].cs_weblog_Threads_GetThreadSet
(
	@SectionID int,
	@PageIndex int, 
	@PageSize int,
	@sqlPopulate ntext,
	@UserID int,
	@IncludeCategories 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,
	ThreadID int
)

--CREATE INDEX page_index ON #PageIndex(IndexID)

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

SET @TotalRecords = @@rowcount

SET ROWCOUNT @RowsToReturn

SELECT 
	jT.*,
	jP.PostID, jP.ThreadID, jP.ParentID, jP.PostAuthor, jP.UserID, jP.SectionID, jP.PostLevel, jP.SortOrder, jP.Subject, jP.PostDate, jP.IsApproved,
	jP.IsLocked, jP.IsIndexed, jP.TotalViews, jP.Body, jP.FormattedBody, jP.IPAddress, jP.PostType, jP.EmoticonID, jP.SettingsID, jP.AggViews,
	jP.PropertyNames as PostPropertyNames, jP.PropertyValues as PostPropertyValues,
	jB.*,
	HasRead = 1,
	UserName = jT.PostAuthor,
	Replies = jT.TotalReplies -- (SELECT COUNT(P2.PostID) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = jP.PostID AND P2.PostLevel != 1)
FROM 
	#PageIndex jPI
	JOIN cs_Threads jT ON jPI.ThreadID = jT.ThreadID
	JOIN cs_Posts jP ON jT.ThreadID = jP.ThreadID
	JOIN cs_weblog_Posts jB ON jP.PostID = jB.PostID
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], jP.PostID
FROM 
	#PageIndex jPI
	JOIN cs_Threads jT ON jPI.ThreadID = jT.ThreadID
	JOIN cs_Posts jP ON jT.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 jT.SettingsID = @SettingsID and jP.SettingsID = @SettingsID


End

DROP TABLE #PageIndex







GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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