CS中的SQL分页

DECLARE @PageSize int
DECLARE @PageIndex int

SET @PageSize = 20
SET @PageIndex = 10

DECLARE @LowerBound int
DECLARE @UpperBound int
DECLARE @ReturnRows int

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

-- Set the page bounds
SET @LowerBound = @PageSize * @PageIndex
SET @UpperBound = @LowerBound + @PageSize + 1

-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
 IndexID int IDENTITY (1, 1) NOT NULL,
 KeyID int
)

-- Sort
INSERT INTO #PageIndex (KeyID)
SELECT PostID FROM cs_Posts (nolock) ORDER BY PostID DESC

SELECT
 cs_Posts.*
FROM
 cs_Posts,
 #PageIndex
WHERE
 #PageIndex.IndexID > @LowerBound AND
 #PageIndex.IndexID < @UpperBound AND
 cs_Posts.PostID = #PageIndex.KeyID

DROP Table #PageIndex