cs_roller_getposts.prc

community server 源码

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROC [dbo].cs_Roller_GetPosts
	@UserID INT,
	@PageIndex int = 0, 
	@PageSize int = 25,
	@TotalRecords int output
AS

SET Transaction Isolation Level Read UNCOMMITTED

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

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


-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

CREATE TABLE #PageIndex 
(
	IndexID int IDENTITY (1, 1) NOT NULL,
	FeedPostId int
)

Insert #PageIndex (FeedPostId)
Select fp.FeedPostId
FROM cs_FeedPost fp,
	cs_Feed f,
	cs_FolderFeed ff
WHERE fp.FeedId = f.FeedId
  AND f.FeedId = ff.FeedId
  AND ff.UserId = @UserId
ORDER BY fp.FeedPostId DESC 
-- changed this to Id instead of date. 
-- Do all feeds return acurate dates, do we care? 
-- My guess is users want to see what was most recently added to the site.

SET @TotalRecords = @@rowcount

SELECT 	f.Title AS 'FeedTitle',
	f.Link AS 'FeedLink',
	fp.FeedPostId,
	fp.FeedId,
	fp.Author,
	fp.Title,
	fp.Description,
	fp.Source,
	fp.GuidName,
	fp.GuidIsPermaLink,
	fp.Link,
	fp.PubDate,
	fp.CommentsUrl,
	fp.EnclosureUrl,
	fp.EnclosureLength,
	fp.EnclosureType,
	fp.Creator,
	fp.CommentApiUrl,
	fp.CommentRssUrl,
	fp.CommentCount
FROM cs_FeedPost fp,
	cs_Feed f,
	cs_FolderFeed ff,
	#PageIndex fPI
WHERE fp.FeedId = f.FeedId
  AND f.FeedId = ff.FeedId
  AND ff.UserId = @UserId
  AND fPI.FeedPostID = fp.FeedPostID
  AND fPI.IndexID > @PageLowerBound
  AND fPI.IndexID < @PageUpperBound
ORDER BY fPI.IndexID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE ON [dbo].cs_Roller_GetPosts  TO PUBLIC
GO