⭐ 欢迎来到虫虫下载站! | 📦 资源下载 📁 资源专辑 ℹ️ 关于我们
⭐ 虫虫下载站

📄 cs_weblog_postset.prc

📁 解压即可使用
💻 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 + -