sql存储过程分页

exec [dbo].[AJ_CTFX_Page] 10,1
alter PROCEDURE [dbo].[AJ_CTFX_Page]
--@FrequentFlyerNameNVARCHAR(128),
--@PapersNumberNVARCHAR(256),
--@CustomerId UNIQUEIDENTIFIER,
@PageSize INT = 10,
@PageIndex INT = 0,
@TotalRecords INT= 0 OUTPUT
AS
BEGIN
SET NOCOUNT ON


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

SET @RowsToReturn = @PageSize * @PageIndex
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

CREATE TABLE #PageIndex 
(
IndexId INT IDENTITY (1, 1) NOT NULL,
ID INT NOT NULL
)


INSERT INTO #PageIndex ( ID )
SELECT ID
FROM [dbo].[Papers_AnswerDetailed] WITH(NOLOCK)
WHERE
(
( [IsRight]=1 )
--AND
--( @FrequentFlyerName IS NULL OR ( PATINDEX('%'+@FrequentFlyerName+'%',Name)>0 OR PATINDEX('%'+@FrequentFlyerName+'%',PinyinName)>0) )
--AND
--( @PapersNumber IS NULL OR [PapersNumber]=@PapersNumber )
--AND
--( @CustomerId IS NULL OR [CreateBy]=@CustomerId )
--AND
--( ParentFlyerId = 0 )
)
ORDER BY [ID] DESC

SELECT @TotalRecords=COUNT(IndexId) FROM #PageIndex
SET ROWCOUNT @RowsToReturn

SELECT
[ad].[TestID],[ad].[AnswerJson],[t].[AnswerCount],[t].[AnswerErrorCount],[t].[AnswerRightCount],[t].[AnswerJson],[t].[Caption]
FROM
[dbo].[Papers_AnswerDetailed] [ad]
INNER JOIN #PageIndex [pi] ON [ad].ID=[pi].ID
INNER JOIN [dbo].[Papers_Test] [t] ON [ad].[TestID]=[t].[ID]
WHERE
[pi].IndexId > @PageLowerBound
AND
[pi].IndexId < @PageUpperBound
ORDER BY ad.ID DESC

SET ROWCOUNT 0


DROP TABLE #PageIndex

END