分页存储过程

 分页存储过程

网上流传的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