一段MSSQL分页的存储过程,先收藏了,备用。

CREATE PROCEDURE GTNews_Search
(
             @SearchSQL nvarchar(4000),
 @RecordCountSQL nvarchar(4000),
 @PageIndex int=0,
 @PageSize int=25
)
AS
BEGIN


DECLARE @StartTime datetime
 DECLARE @RowsToReturn int
 DECLARE @PageLowerBound int
 DECLARE @PageUpperBound int
 DECLARE @Count int

-- Used to calculate cost of query
 SET @StartTime = GetDate()

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

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

-- Create a temp table to store the results in
 CREATE TABLE #SearchResults
 (
  IndexID int IDENTITY (1, 1) NOT NULL,
  NewsID int
 )

-- Fill the temp table
 INSERT INTO #SearchResults (NewsID)
 exec (@SearchSQL)

-- SELECT actual search results from this table
 SELECT
  D.*,catname=(SELECT title FROM GT_news_cat WHERE id=D.catid)
 FROM
  GT_news_data D,
  #SearchResults R
 WHERE
  R.NewsID = D.ID AND
  R.IndexID > @PageLowerBound AND
  R.IndexID < @PageUpperBound

 -- Do we need to return a record estimate?
 exec (@RecordCountSQL) 


            SELECT Duration = GetDate() - @StartTime
END
GO

最后修改:2012 年 03 月 02 日 09 : 05 PM
如果觉得我的文章对你有用,请随意赞赏