SQL 2000 的分頁

在 Laravel 中，雖然有方便的分頁功能，但必須是 SQL 2005 以上。使用 SQL 2000，就只好自行想辦法了。

下面是參考網路找來的作法，參見 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 秒。