📄 cs_weblog_postset.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_PostSet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_weblog_PostSet]
GO
CREATE PROCEDURE [dbo].cs_weblog_PostSet
(
@SectionID int,
@PostID int,
@PostName nvarchar(512) = null,
@PageIndex int,
@PageSize int,
@UserID int,
@ReturnFullThread bit,
@IncludeCategories bit,
@TotalRecords int output
)
AS
BEGIN
SET Transaction Isolation Level Read UNCOMMITTED
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @ThreadID int
-- Get the ThreadID
if(@PostName is null)
Begin
SELECT
@ThreadID = ThreadID
FROM
cs_Posts
WHERE
PostID = @PostID and SectionID = @SectionID
End
Else
Begin
SELECT
@ThreadID = P.ThreadID, @PostID = P.PostID
FROM
cs_Posts P, cs_weblog_Posts B
WHERE
P.PostID = B.PostID and P.SectionID = @SectionID and B.PostName = @PostName
End
--Now we need the main post
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,
T.*, B.*,
T.IsLocked,
T.IsSticky,
Username = P.PostAuthor,
EditNotes = null, -- (SELECT EditNotes FROM cs_PostEditNotes WHERE PostID = P.PostID),
AttachmentFilename = null, -- ISNULL ( (SELECT [FileName] FROM cs_PostAttachments WHERE PostID = P.PostID), ''),
Replies = t.TotalReplies, -- (SELECT COUNT(P2.PostID) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1 and P2.IsApproved = 1),
IsModerator = 0, --(SELECT count(UserID) from cs_Moderators where UserID = @UserID),
HasRead = 0 -- not used
FROM
cs_Posts P (nolock),
cs_Threads T,
cs_weblog_Posts B
WHERE
P.PostID = @PostID AND
B.PostID = P.PostID AND
T.ThreadID = P.ThreadID
--Do we want/need categories
IF @IncludeCategories = 1
Begin
Select [Name]
FROM cs_Post_Categories PC, cs_Posts_InCategories PIC
Where PC.CategoryID = PIC.CategoryID AND PIC.PostID = @PostID
End
--Are we going to honor paging?
if @ReturnFullThread = 0
Begin
-- First set the rowcount
DECLARE @RowsToReturn int
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,
PostID int
)
INSERT INTO #PageIndex (PostID)
SELECT PostID FROM cs_Posts (nolock) WHERE IsApproved = 1 AND ThreadID = @ThreadID and PostLevel > 1 ORDER BY PostDate
Set @TotalRecords = @@rowcount
SET ROWCOUNT @RowsToReturn
-- 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,
T.*, B.*, #PageIndex.*,
T.IsLocked,
T.IsSticky,
Username = P.PostAuthor,
EditNotes = null, --(SELECT EditNotes FROM cs_PostEditNotes WHERE PostID = P.PostID),
AttachmentFilename = null,
Replies = 0,
IsModerator = 0, --(SELECT count(UserID) from cs_Moderators where UserID = @UserID),
HasRead = 0 -- not used
FROM
cs_Posts P (nolock),
cs_Threads T,
cs_weblog_Posts B,
#PageIndex
WHERE
P.PostID = #PageIndex.PostID AND
T.ThreadID = P.ThreadID AND
B.PostID = P.PostID AND
#PageIndex.IndexID > @PageLowerBound AND
#PageIndex.IndexID < @PageUpperBound
ORDER BY
IndexID
--SELECT @TotalRecords = count(*) FROM #PageIndex
DROP TABLE #PageIndex
END
ELSE --No Paging, just get all of the comments/trackbacks
BEGIN
-- 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,
B.*,t.*,
t.IsLocked,
t.IsSticky,
Username = P.PostAuthor,
EditNotes = null, -- (SELECT EditNotes FROM cs_PostEditNotes WHERE PostID = P.PostID),
AttachmentFilename = null, -- ISNULL ( (SELECT [FileName] FROM cs_PostAttachments WHERE PostID = P.PostID), ''),
Replies = 0, -- (SELECT COUNT(P2.PostID) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
IsModerator = 0, --(SELECT count(UserID) from cs_Moderators where UserID = @UserID),
HasRead = 0 -- not used
FROM
cs_Posts P,
cs_weblog_Posts B,
cs_Threads t
WHERE
B.PostID = P.PostID AND p.ThreadID = t.ThreadID AND
T.ThreadID = @ThreadID AND P.PostLevel > 1 AND P.IsApproved = 1
ORDER BY
P.PostID
SELECT @TotalRecords = Count(PostID) FROM cs_Posts (nolock) WHERE IsApproved = 1 AND ThreadID = @ThreadID and PostLevel <> 1
END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
grant execute on [dbo].[cs_weblog_PostSet] to public
go
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -