cs_gallery_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_gallery_Threads_GetThreadSet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_gallery_Threads_GetThreadSet]
GO



CREATE    procedure [dbo].cs_gallery_Threads_GetThreadSet
(
	@SectionID int,
	@PageIndex int, 
	@PageSize int,
	@sqlPopulate nvarchar(4000),
	@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 ROWCOUNT @RowsToReturn

SELECT 
	jT.*,
	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, 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,
	jA.[FileName] as AttachmentFilename,
	HasRead = 1,
	UserName = jT.PostAuthor
FROM 
	#PageIndex jPI
	JOIN cs_Threads jT ON jPI.ThreadID = jT.ThreadID
	JOIN cs_Posts jP ON jPI.ThreadID = jP.ThreadID
	join cs_PostAttachments jA ON jA.PostID = jP.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

Select @TotalRecords = Count(*) FROM #PageIndex

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_gallery_Threads_GetThreadSet to public
go