3)使用子查询
CREATE PROCEDURE [sp_ForumThreadPagination2]
@nPageSize INT,
@nPageNo INT,
@PageCount INT OUTPUT,
@RecordCount INT OUTPUT
AS
SET NOCOUNT ON
/*
SELECT @RecordCount = COUNT(*) FROM [ForumTopic] WHERE [ForumId] = 1
IF (@RecordCount % @nPageSize) > 0 BEGIN
SET @PageCount = (@RecordCount / @nPageSize) + 1
END
ELSE BEGIN
SET @PageCount = (@RecordCount / @nPageSize)
END
*/
IF @nPageNo = 1 BEGIN
SET ROWCOUNT @nPageSize
SELECT [ForumId], [TopicId] AS [ThreadId] FROM [ForumTopic] WHERE [ForumId] = 1 ORDER BY [TopicId] DESC
END
ELSE BEGIN
DECLARE @RowAffected INT
DECLARE @SQL VARCHAR(8000)
SET @RowAffected = @nPageSize * @nPageNo
--这里最好不用D-SQL, 也是没有时间去想
SET @SQL = 'SELECT TOP ' + CONVERT(VARCHAR, @nPageSize) + ' * FROM
(SELECT TOP ' + CONVERT(VARCHAR, @RowAffected) + ' [ForumId], [TopicId] FROM [ForumTopic] WHERE [ForumId] = 1 ORDER BY [TopicId] DESC) [TEMP]
ORDER BY [TopicId] ASC'
EXEC(@SQL)
END
GO
2)使用游标
CREATE PROCEDURE [sp_ForumThreadPagination1]
@nPageSize INT,
@nPageNo INT,
@PageCount INT OUTPUT,
@RecordCount INT OUTPUT
AS
SET NOCOUNT ON
/*
SELECT @RecordCount = COUNT(*) FROM [ForumTopic] WHERE [ForumId] = 1
IF (@RecordCount % @nPageSize) > 0 BEGIN
SET @PageCount = (@RecordCount / @nPageSize) + 1
END
ELSE BEGIN
SET @PageCount = (@RecordCount / @nPageSize)
END
*/
IF @nPageNo = 1 BEGIN
SET ROWCOUNT @nPageSize
SELECT [ForumId], [TopicId] AS [ThreadId] FROM [ForumTopic] WHERE [ForumId] = 1 ORDER BY [TopicId] DESC
END
ELSE BEGIN
DECLARE @StartKeyFieldValue INT
DECLARE @EndKeyFieldValue INT
DECLARE @nRowAffected INT
DECLARE @nAbsoluteStartPos INT
DECLARE @nAbsoluteEndPos INT
SET @nRowAffected = @nPageSize * @nPageNo
SET ROWCOUNT @nRowAffected
DECLARE [CertainCursor] CURSOR LOCAL STATIC READ_ONLY FOR
SELECT [TopicId] FROM [ForumTopic] WHERE [ForumId] = 1
OPEN [CertainCursor]
SET @nAbsoluteEndPos = @nRowAffected
--取得最后一页时可能会有BUG,没有时间研究
FETCH NEXT FROM [CertainCursor] INTO @StartKeyFieldValue
IF @@FETCH_STATUS = 0 BEGIN
SET @nAbsoluteStartPos = (@nPageNo - 1) * @nPageSize + 1
FETCH ABSOLUTE @nAbsoluteStartPos FROM [CertainCursor] INTO @StartKeyFieldValue
IF @@FETCH_STATUS <> 0 BEGIN
RETURN -1
END
FETCH ABSOLUTE @nAbsoluteEndPos FROM [CertainCursor] INTO @EndKeyFieldValue
IF @@FETCH_STATUS <> 0 BEGIN
RETURN -2
END
END
CLOSE [CertainCursor]
DEALLOCATE [CertainCursor]
SELECT [ForumId], [TopicId] FROM [ForumTopic] WHERE [ForumId] = 1 AND [TopicId] BETWEEN @StartKeyFieldValue AND @EndKeyFieldValue
RETURN 0
END
GO
1)临时表存储过程
CREATE PROCEDURE [sp_ForumThreadPagination]
@nPageSize INT,
@nPageNo INT,
@PageCount INT OUTPUT,
@RecordCount INT OUTPUT
AS
SET NOCOUNT ON
/*
SELECT @RecordCount = COUNT(*) FROM [ForumTopic] WHERE [ForumId] = 1
IF (@RecordCount % @nPageSize) > 0 BEGIN
SET @PageCount = (@RecordCount / @nPageSize) + 1
END
ELSE BEGIN
SET @PageCount = (@RecordCount / @nPageSize)
END
*/
IF @nPageNo = 1 BEGIN
SET ROWCOUNT @nPageSize
SELECT [ForumId], [TopicId] AS [ThreadId] FROM [ForumTopic] WHERE [ForumId] = 1 ORDER BY [TopicId] DESC
END
ELSE BEGIN
CREATE TABLE [#TempCertainTable](
[TempId] INT IDENTITY(1, 1),
[ForumId] INT,
[ThreadId] INT
)
DECLARE @nRowAffected INT
SET @nRowAffected = @nPageSize * @nPageNo
SET ROWCOUNT @nRowAffected
INSERT INTO [#TempCertainTable] ([ForumId], [ThreadId])
SELECT [ForumId], [TopicId]
FROM [ForumTopic]
WHERE [ForumId] = 1
SELECT [ForumId], [ThreadId] FROM [#TempCertainTable] WHERE [TempId] > (@nPageNo - 1) * @nPageSize AND [TempId] < @nPageNo * @nPageSize + 1
DROP TABLE [#TempCertainTable]
END
GO