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



CREATE  PROCEDURE [dbo].cs_weblog_Post_Get --11, 36, 0, 1, 0
(

	@PostID	int,
	@IncludeCategories bit,
	@IsApprovedFilter bit,
	@SettingsID int
)
AS
BEGIN

DECLARE	@SectionID int
DECLARE @ThreadID int 
DECLARE @SortOrder int
DECLARE @IsApproved bit


SELECT 
	@SectionID = SectionID,
	@ThreadID = ThreadID, 
	@SortOrder=SortOrder,
	@IsApproved = IsApproved
FROM 
	cs_Posts (nolock) 
WHERE 
	PostID = @PostID and  SettingsID = @SettingsID


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.*, U.*, B.*,
	T.ThreadDate,
	T.StickyDate,
	T.IsLocked,
	T.IsSticky,
	HasRead = 0,
	EditNotes = null, -- (SELECT EditNotes FROM cs_PostEditNotes WHERE PostID = P.PostID),
	IndexInThread = 0,  -- (SELECT Count(PostID) FROM cs_Posts P1 WHERE IsApproved = 1 AND ThreadID = @ThreadID AND SortOrder <= (SELECT SortOrder FROM cs_Posts where PostID = @PostID)),
	AttachmentFilename = null, -- ISNULL ( (SELECT [FileName] FROM cs_PostAttachments WHERE PostID = P.PostID), ''),
	IsModerator = 0, -- (SELECT Count(*) FROM cs_Moderators WHERE UserID = U.cs_UserID),
	Replies = T.TotalReplies, -- (SELECT COUNT(*) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
	PrevThreadID = 0,
	NextThreadID = 0,
	UserIsTrackingThread = 0,
	B.PostConfig, B.BlogPostType, B.TitleUrl, B.Excerpt, B.PostName,
	IsTracked = Convert(bit,0) -- Convert(bit, (Select Count(*) FROM cs_TrackedThreads where UserID = @UserID and ThreadID = P.ThreadID))
FROM 
	cs_Posts P,
	cs_Threads T,
	cs_vw_Users_FullUser U,
	cs_weblog_Posts B
WHERE 
	P.PostID = @PostID AND P.SettingsID = @SettingsID and T.SettingsID = @SettingsID AND
	B.PostID = P.PostID AND U.SettingsID = @SettingsID AND
	P.ThreadID = T.ThreadID AND
	P.UserID = U.cs_UserID AND
	P.IsApproved <> case @IsApprovedFilter when 1 then 0 else -1 END

	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
End

/*
	SELECT
		P.SectionID, P.PostID, T.TotalReplies,P.UserID, T.TotalViews, T.MostRecentPostID, T.ThreadID,
	 	P.Subject, P.Body, T.MostRecentPostAuthor, T.MostRecentPostAuthorID, P.PostDate,
		T.IsLocked, T.ThreadDate, T.StickyDate, T.TotalRatings, T.RatingSum, T.ThreadEmoticonID, T.ThreadStatus,
		P.FormattedBody, B.PostConfig, B.BlogPostType, B.TitleUrl, B.Excerpt, B.PostName,
		T.IsSticky,
		,
		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 = 0, --(SELECT count(UserID) from cs_Moderators where UserID = @UserID),
		HasRead = 0, -- not used,
		
		P.*, B.PostConfig, B.BlogPostType, B.TitleUrl, B.Excerpt, B.PostName,
		IsTracked = Convert(bit, (Select Count(*) FROM cs_TrackedThreads where UserID = @UserID and ThreadID = P.ThreadID)),
		T.ThreadDate,Username = P.PostAuthor,
		Replies = (SELECT COUNT(P2.PostID) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = @PostID AND P2.PostLevel != 1)
	FROM 
		cs_Posts P (nolock),
		cs_Threads T,
		cs_vw_Users_FullUser U,
		cs_weblog_Posts B
	WHERE 
		P.PostID = @PostID AND
		B.PostID = P.PostID AND
		P.UserID = U.cs_UserID AND
		T.ThreadID = P.ThreadID AND 
		P.IsApproved <> case @IsApproved when 1 then 0 else -1 END


--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


END

*/


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_weblog_Post_Get] to public
go