cs_weblog_feedback_get.prc

解压即可使用

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_weblog_Feedback_Get]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_weblog_Feedback_Get]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO



CREATE  PROCEDURE [dbo].cs_weblog_Feedback_Get
(
	 @SectionID int
	,@BlogPostType int = 12 -- 4 and 8 are comment and traceback types
	,@UsePaging bit = 0
	,@PageIndex int = 0
	,@PageSize int = 0
	,@SettingsID int
	,@TotalRecords int output
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

-- Return Fields:
-- PostID, PostAuthor, Subject, PostDate, IsApproved, TitleUrl, Body

	SELECT
		@TotalRecords = Count( P.PostID )		
	FROM 
		cs_Posts P (nolock)
			INNER JOIN
		cs_weblog_Posts B
			On P.PostID = B.PostID
	WHERE
		P.SectionID = @SectionID and P.SettingsID = @SettingsID
		AND
		( ( B.BlogPostType & @BlogPostType ) <> 0 )


IF @UsePaging = 0
BEGIN
	-- Just select all the feedback for the blog
	SELECT
		 P.PostID
		,P.PostAuthor
		,P.Subject
		,P.PostDate
		,P.IsApproved
		,B.TitleUrl
		,P.Body
	FROM 
		cs_Posts P (nolock)
			INNER JOIN
		cs_weblog_Posts B
			On P.PostID = B.PostID
	WHERE
		P.SectionID = @SectionID and P.SettingsID = @SettingsID
		AND
		( ( B.BlogPostType & @BlogPostType ) <> 0 )
	ORDER BY
		P.PostDate DESC




END
ELSE -- We need to get paged data
BEGIN
	-- We'll Sort by newest first

	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound int
	DECLARE @RowsToReturn int

	-- First set the rowcount
	SET @RowsToReturn = @PageSize * (@PageIndex + 1)
	SET ROWCOUNT @RowsToReturn

	-- 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
		 P.PostID
	FROM 
		cs_Posts P (nolock)
			INNER JOIN
		cs_weblog_Posts B
			On P.PostID = B.PostID
	WHERE
		p.SectionID = @SectionID and p.SettingsID = @SettingsID
		AND
		( ( B.BlogPostType & @BlogPostType ) <> 0 )
	ORDER BY
		 P.PostDate DESC
		,P.PostID DESC



	-- now return the results:
	SELECT
		 P.PostID
		,P.PostAuthor
		,P.Subject
		,P.PostDate
		,P.IsApproved
		,B.TitleUrl
		,P.Body
	FROM 
		 cs_Posts P (nolock)
		,cs_weblog_Posts B
		,#PageIndex
	WHERE
		P.PostID = #PageIndex.PostID and P.SettingsID = @SettingsID
		AND
		B.PostID = P.PostID
		AND
		p.SectionID = @SectionID
		AND
		( ( B.BlogPostType & @BlogPostType ) <> 0 )
		AND
		#PageIndex.IndexID > @PageLowerBound
		AND
		#PageIndex.IndexID < @PageUpperBound
	ORDER BY
		 P.PostDate DESC
		,P.PostID DESC


	-- clean up:
	DROP TABLE #PageIndex
	SET rowcount 0 

END

END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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