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





CREATE PROCEDURE dbo.cs_forums_Moderate_PostSet
(
	@SectionID		int,
	@PageIndex 		int,
	@PageSize 		int,
	@SortBy 		int,
	@SortOrder 		bit,
	@UserID 		int,
	@ReturnRecordCount 	bit,
	@SettingsID		int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @ThreadID 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 cs_Posts P (nolock) WHERE IsApproved = 0 AND SectionID = @SectionID and SettingsID = @SettingsID ORDER BY PostDate

ELSE IF @SortBy = 0 AND @SortOrder = 1
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts P (nolock) WHERE IsApproved = 0 AND SectionID = @SectionID and SettingsID = @SettingsID ORDER BY PostDate DESC

-- Select the individual posts
SELECT
	P.PostID, P.ThreadID, P.ParentID, P.PostAuthor, P.UserID, P.SectionID, P.PostLevel, P.SortOrder, P.Subject, P.PostDate, P.IsApproved,
	P.IsLocked, P.IsIndexed, P.TotalViews, P.Body, P.FormattedBody, P.IPAddress, P.PostType, P.PostMedia, P.EmoticonID, P.SettingsID, P.AggViews,
	P.PropertyNames as PostPropertyNames, P.PropertyValues as PostPropertyValues,
	P.PostConfiguration,P.UserTime, P.ApplicationPostType, P.PostName, P.PostStatus, P.SpamScore,
	P.Points as PostPoints, P.RatingSum as PostRatingSum, P.TotalRatings as PostTotalRatings,
	T.*, U.*, #PageIndex.*,
	T.IsLocked,
	T.IsSticky,
	Username = P.PostAuthor,
	EditNotes = (SELECT EditNotes FROM cs_PostEditNotes WHERE PostID = P.PostID),
	AttachmentFilename = ISNULL ( (SELECT [FileName] FROM cs_PostAttachments WHERE PostID = P.PostID), ''),
	Replies = (SELECT COUNT(P2.PostID) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
	IsModerator = (SELECT count(UserID) from cs_Moderators where UserID = @UserID),
	HasRead = 0 -- not used
FROM 
	cs_Posts P (nolock),
	cs_Threads T,
	cs_vw_Users_FullUser U,
	#PageIndex
WHERE 
	P.PostID = #PageIndex.PostID AND
	P.UserID = U.cs_UserID AND
	T.ThreadID = P.ThreadID AND
	#PageIndex.IndexID > @PageLowerBound AND
	#PageIndex.IndexID < @PageUpperBound and U.SettingsID = @SettingsID
ORDER BY
	IndexID
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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