分页存储过程

网上流传的Web分页存储过程大体分为两种:
一、（利用ID大于多少和SELECT TOP分页）

SELECT TOP 页大小 * FROM TestTable
WHERE (ID > (SELECT MAX(id) FROM (SELECT TOP 页大小*页数 id FROM 表 ORDER BY id) AS T))ORDER BY ID

二、（利用Not In和SELECT TOP分页）

SELECT TOP 页大小 * FROM TestTable
WHERE (ID NOT IN (SELECT TOP 页大小*页数 id FROM 表 ORDER BY id)) ORDER BY ID

众所周知这两个存储过程都有自己的优劣，方案一效率最高但是不够灵活，在实际应用中如果要使用其它指定的字段排序时就无能力了。方案二中使用了NOT IN来过滤数据，这会使率大打折扣。那么有没有界于两者之间的方呢？我们可以使用NOT EXISTS来代替NOT IN.

SELECT TOP 页大小 * FROM TestTable WHERE NOT EXISTS(SELECT * FROM (SELECT TOP 页大小*页数 ID FROM  TestTable) T  WHERE T.ID=TestTable.ID)

不要以为这样就大功告成，这才完成了一半.不知大家有没有碰到这种情况：当排序字段有多个相同值时，SELECT TOP N 得到的结果与SELECT TOP M (N<>M)得到的结果排序顺序是不一样的,这就会引响正常的分页显示所以对上面方法还需要做进一步调整,我们除了需要按指定的列进行排序外还要让他按唯一键排序.这样如果指定的列有多个相同值也可以确保它们在整张表的中的位置不变.

SELECT TOP 页大小 * FROM TestTable WHERE NOT EXISTS(SELECT * FROM (SELECT TOP 页大小*页数 ID FROM TestTable ORDER BY 排序列 ASC/DESC,唯一键列) T WHERE T.ID=TestTable.ID) ORDER BY 排序列 ASC/DESC,唯一键列

下面是存储过程:希望大家试用,给出意见

CREATE     PROC  GetCurrentPageData
(
     @tableName   VARCHAR ( 100 ),         -- 表名
     @primaryKey   VARCHAR ( 100 ),        -- 主键
     @filter   VARCHAR ( 800 ) = '' ,         -- 查询条件(不用写WHERE)
     @fields   VARCHAR ( 800 ) = ' * ' ,        -- 查询字段(用逗号分隔多个字段)
     @orderField   VARCHAR ( 800 ) = '' ,     -- 排序列名
     @order   BIT = 0 ,                    -- 排序("1"降序,"0"升序)
     @pageIndex   INT = 1 ,                -- 页面索引
     @pageSize   INT = 15 ,                -- 每页面记录数
     @pageCount   INT  OUTPUT,           -- 页面总数
     @recordCount   INT  OUTPUT          -- 记录总数
)
AS
DECLARE   @SQL   NVARCHAR ( 4000 ), @subFilter   VARCHAR ( 200 ), @parentFilter   VARCHAR ( 200 )

-- 组合查询条件字符串
IF   @filter   IS   NOT   NULL   AND   LTRIM ( @filter ) != ''
     BEGIN
         SET   @subFilter = '  WHERE  ' + @filter + '   '
         SET   @parentFilter = '  AND  ' + @filter + '   '
     END
ELSE
     BEGIN
         SET   @subFilter = ''
         SET   @parentFilter = ''
     END

-- 组合排序字符串
DECLARE   @orderStr   VARCHAR ( 200 )
IF   @orderField   IS   NULL   OR   RTRIM ( LTRIM ( @orderField )) = ''   OR   @orderField = @primaryKey
     BEGIN
         IF   @order = 1
             SET   @orderStr = '  ORDER BY  ' + @primaryKey + '  DESC  '
         ELSE
             SET   @orderStr = '  ORDER BY  ' + @primaryKey + '  ASC  '
     END
ELSE
     BEGIN
         IF   @order = 1
             SET   @orderStr = '  ORDER BY  ' + @orderField + '  DESC, ' + @primaryKey
         ELSE
             SET   @orderStr = '  ORDER BY  ' + @orderField + '  ASC, ' + @primaryKey
     END
    
SET   @SQL = ' SELECT TOP  ' + CAST ( @pageSize   AS   VARCHAR ) + '   ' + @fields + '  FROM  ' + @tableName + '  WHERE NOT EXISTS(SELECT * FROM(SELECT TOP  ' + CAST ((( @pageIndex - 1 ) * @pageSize )  AS   VARCHAR ) + '   ' + @primaryKey + '  FROM  ' + @tableName + @subFilter + @orderStr + ' ) T WHERE T. ' + @primaryKey + ' = ' + @tableName + ' . ' + @primaryKey + ' ) ' + @parentFilter + @orderStr
    
EXEC  ( @SQL )
-- 查询总页数
SET   @SQL = ' SELECT @pageCount=CEILING((COUNT(*)+0.0)/ ' + CAST ( @pageSize   AS   VARCHAR ) + ')  FROM  ' + @tableName + @subFilter
EXEC  sp_executesql  @SQL ,N ' @pageCount INT OUTPUT ' , @pageCount  OUTPUT
-- 查询总计录条数
SET   @SQL = ' SELECT @recordCount=COUNT(*) FROM  ' + @tableName + @subFilter
EXEC  sp_executesql  @SQL ,N ' @recordCount INT OUTPUT ' , @recordCount  OUTPUT