![](/Images/OutliningIndicators/ContractedBlock.gif)
cs_forums_Moderate_PostSet
ALTER 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
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*设置事务隔离级别,Read UNCOMMITTED 执行脏读或 0 级隔离锁定,这表示不发出共享锁,
也不接受排它锁。当设置该选项时,可以对数据执行未提交读或脏读;在事务结束前可以更
改数据内的数值,行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有
语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。*/
![](/Images/OutliningIndicators/None.gif)
BEGIN
![](/Images/OutliningIndicators/None.gif)
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @ThreadID int
![](/Images/OutliningIndicators/None.gif)
-- Set the page bounds
-- 设置页面绑定
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
![](/Images/OutliningIndicators/None.gif)
-- Create a temp table to store the select results
-- 创建个临时表来存储查询结果(为页面绑定用的)
CREATE TABLE #PageIndex
(
IndexID int IDENTITY (1, 1) NOT NULL,
PostID int
)
![](/Images/OutliningIndicators/None.gif)
-- 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
![](/Images/OutliningIndicators/None.gif)
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
![](/Images/OutliningIndicators/None.gif)
-- 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.EmoticonID, P.SettingsID, P.AggViews,
P.PropertyNames as PostPropertyNames, P.PropertyValues as PostPropertyValues,
P.PostConfiguration,P.UserTime, P.ApplicationPostType, P.PostName,
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
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
posted on
2006-09-20 15:49
MainIsUsed
阅读(
194)
评论()
编辑
收藏
举报