下面是參考網路找來的作法，參見 Efficient Paging (Limit) Query in SQLServer 2000?。
-----------------
CREATE PROCEDURE [dbo].[GetPage]
@pTableName VARCHAR(30),
@pColumns VARCHAR(200) = '*',
@pFilter VARCHAR(200) = '',
@pSort VARCHAR(200) = '',
@pPage INT = 1,
@pPageRows INT = 10
AS
SET NOCOUNT ON
DECLARE @vSQL VARCHAR(4000)
DECLARE @vTempTable VARCHAR(30)
DECLARE @vRowStart INT
DECLARE @vTotalRows INT
SET @vTempTable = '##Tmp' + CAST(DATEPART(YYYY, GETDATE()) AS VARCHAR(4)) +
CAST(DATEPART(MM, GETDATE()) AS VARCHAR(2)) +
CAST(DATEPART(DD, GETDATE()) AS VARCHAR(2)) +
CAST(DATEPART(HH, GETDATE()) AS VARCHAR(2)) +
CAST(DATEPART(MI, GETDATE()) AS VARCHAR(2)) +
CAST(DATEPART(SS, GETDATE()) AS VARCHAR(2)) +
CAST(DATEPART(MS, GETDATE()) AS VARCHAR(3))
SET @vSQL = 'SELECT ' + @pColumns + ', IDENTITY(INT, 1, 1) AS ROWID INTO ' + @vTempTable + ' FROM ' + @pTableName
IF @pFilter != '' AND @pFilter IS NOT NULL
SET @vSQL = @vSQL + ' WHERE ' + @pFilter
IF @pSort != '' AND @pSort IS NOT NULL
SET @vSQL = @vSQL + ' ORDER BY ' + @pSort
EXECUTE (@vSQL)
-- Get the total number of rows selected
SET @vTotalRows = @@ROWCOUNT
-- If page number = 0, set it to the first page
IF @pPage = 0
SET @pPage = 1
-- If page number is beyond the last page, set page to the last page
IF (@pPage * @pPageRows) > @vTotalRows
BEGIN
SET @pPage = @vTotalRows / @pPageRows
IF (@vTotalRows % @pPageRows) != 0
SET @pPage = @pPage + 1
END
SET @vRowStart = ((@pPage - 1) * @pPageRows) + 1
SET @vSQL = 'SELECT * FROM ' + @vTempTable + ' WHERE ROWID BETWEEN ' + CAST(@vRowStart AS VARCHAR(10)) +
' AND ' + CAST((@vRowStart + @pPageRows - 1) AS VARCHAR(10)) + ' ORDER BY ROWID'
EXECUTE (@vSQL)
SET @vSQL = 'DROP TABLE ' + @vTempTable
EXECUTE (@vSQL)
GO
------------------
使用
exec getpage 'ifcrfct', '*', 'ct_cono like ''9%''', 'CT_YYSE desc', 100, 10
一些變數的值
@vTempTable: ##Tmp20171124141845213
@vSQL: SELECT *, IDENTITY(INT, 1, 1) AS ROWID INTO ##Tmp20171124142153370 FROM ifcrfct WHERE ct_cono like '9%' ORDER BY CT_YYSE desc
@vSQL: SELECT * FROM ##Tmp20171124142424353 WHERE ROWID BETWEEN 1741 AND 1750 ORDER BY ROWID
方便就好，先頂著用
一事不煩二主，再加上幾行，會在第二個 result set 傳回總筆數和總頁數
----------------------
DECLARE @vTotalPages INT
SET @vTotalPages = @vTotalRows / @pPageRows
IF (@vTotalRows % @pPageRows) != 0
SET @vTotalPages = @vTotalPages + 1
SELECT @vTotalPages AS TotalPages
-----------------------
主要的花費，是在建立 temp table。
---------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[tmp_test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tmp_test]
GO
select *, identity(int, 1, 1) as rowid into tmp_test from caucgi.tsel
--------------------------
44 萬筆，第一次 10 秒，第二次以後，約 5 秒。
