通用的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                                                                       --  升序
       BEGIN
         SET  @strOrderType  =  '  ORDER BY  ' + @ascColumn + '  ASC '
         SET  @strTemp  =  ' >(SELECT max '
       END

     IF  @currentPage  =  1                                                 --  第一页
       BEGIN
         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
       END

     ELSE                                                                       --  其他页
       BEGIN
         IF  @condition  != ''
           SET  @strSql  =  ' SELECT TOP  ' + STR( @pageSize) + '   ' + @columns + '  FROM  ' + @tableName +
           '  WHERE  ' + @condition + '  AND  ' + @pkColumn + @strTemp + ' ( ' + @pkColumn + ' ) ' + '  FROM (SELECT TOP  ' + STR(( @currentPage - 1) * @pageSize) +
           '   ' + @pkColumn + '  FROM  '  +  @tableName  +  '  where  '  +  @condition + @strOrderType + ' ) AS TabTemp) ' + @strOrderType
         ELSE
           SET  @strSql  =  ' SELECT TOP  ' + STR( @pageSize) + '   ' + @columns + '  FROM  ' + @tableName +
           '  WHERE  ' + @pkColumn + @strTemp + ' ( ' + @pkColumn + ' ) ' + '  FROM (SELECT TOP  ' + STR(( @currentPage - 1) * @pageSize) + '   ' + @pkColumn +
           '  FROM  ' + @tableName + @strOrderType + ' ) AS TabTemp) ' + @strOrderType
       END

   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 '