通用的SQL資料分頁存儲過程

CREATE PROCEDURE spGetPageOfRecords

  @pageSize int = 20,                                      -- 分頁大小

  @currentPage int ,                                  -- 第幾頁

  @columns varchar(1000) = '*',                        -- 需要得到的字段

  @tableName varchar(100),                               -- 需要查詢的表    

  @condition varchar(1000) = '',                      -- 查詢條件, 不用加where關鍵字

  @ascColumn varchar(100) = '',                       -- 排序的字段名 (即 order by column asc/desc)

  @bitOrderType bit = 0,                                 -- 排序的類型 (0為升序,1為降序)

  @pkColumn varchar(50) = ''                             -- 主鍵名稱

AS

BEGIN                                                                                    

  DECLARE @strTemp varchar(300)

  DECLARE @strSql varchar(5000)                            -- 該存儲過程最後執行的語句

  DECLARE @strOrderType varchar(1000)                    -- 排序類型語句 (order by column asc或者order by column desc)

  BEGIN

    IF @bitOrderType = 1                                        -- 降序

      BEGIN

        SET @strOrderType = ' ORDER BY '+@ascColumn+' DESC'

        SET @strTemp = '<(SELECT min'

      END

    ELSE                                                                      -- 升序

        SET @strOrderType = ' ORDER BY '+@ascColumn+' ASC'

        SET @strTemp = '>(SELECT max'

    IF @currentPage = 1                                                -- 第一頁

        IF @condition != ''

          SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+

            ' WHERE '+@condition+@strOrderType

        ELSE

          SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+@strOrderType

    ELSE                                                                      -- 其他頁

        IF @condition !=''

          ' WHERE '+@condition+' AND '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+

          ' '+@pkColumn+' FROM ' + @tableName + ' where ' + @condition+@strOrderType+') AS TabTemp)'+@strOrderType

          ' WHERE '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+' '+@pkColumn+

          ' FROM '+@tableName+@strOrderType+') AS TabTemp)'+@strOrderType

  END

  EXEC (@strSql)

END 

執行代碼如下：

--  Test Employees Table on Northwind database

EXEC spGetPageOfRecords 5, 2, 'EmployeeID, LastName, FirstName, Title, City, Region, Country', 'Employees', '', 'EmployeeID', 0, 'EmployeeID'

--  Test Customers Table on Northwind database

EXEC spGetPageOfRecords 10, 6, 'CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, Country', 'Customers', '', 'CustomerID', 0, 'CustomerID'