SQLSERVER2000 存储过程分页

好久没有用数据库了。最近在做项目时突然发现6年前自己的分页代码有问题的。

这个问题客户居然从来没反馈过。原来是通过主键来分页的，但排序条件加入后其实不能保证主键队列也是排序的，因此 ... WHERE ZID > MAX(...) ... 的做法不对的。

现在改进，使用 NOT IN 的方法。思路是：比如显示第3页，每页10条记录，那么取出30条记录，但是过滤掉前20条，这样排序是解决了，但效率应该降低了。

贴代码：

CREATE PROCEDURE sp_GetPageData
 @sSelect NVARCHAR(100), --select 子句
 @sFrom NVARCHAR(200),   --from 子句
 @sWhere NVARCHAR(200),  --where 子句
 @sOrder NVARCHAR(50),   --order by 子句
 @iCurPageNo int,        --当前页码
 @iPageSize int,         -- 分页大小
 @iRecordCount int out   --输出总数据条目数量  
  
AS

DECLARE @iPageCount int   --分页数量
DECLARE @SQLString NVARCHAR(800)        --总SQL
DECLARE @ParmDefinition NVARCHAR(50)    --参数定义
DECLARE @sWhereIn NVARCHAR(200)         --NOT IN 子句

 IF @iPageSize < 1

   SET @iPageSize = 10

--计算记录数目
SET @SQLString = N'SELECT @iRecordCount = COUNT(*)  FROM ' + @sFrom +  N' WHERE' + @sWhere
--+ ' ORDER BY ' + @sOrder

SET @ParmDefinition = N'@iRecordCount int OUT'
print @SQLString

EXECUTE sp_executesql @SQLString,@ParmDefinition,@iRecordCount OUT
print N'@iRecordCount = ' + CAST(@iRecordCount AS varchar)

if (0 = @iRecordCount)
begin
 print 'data is null'
 return
end

--计算分页
if (@iRecordCount % @iPageSize > 0)
  SET @iPageCount = @iRecordCount / @iPageSize + 1
ELSE
  SET @iPageCount = @iRecordCount / @iPageSize

--保证请求页在页码范围内
if (@iCurPageNo > @iPageCount)
 SET @iCurPageNo = @iPageCount
 
--返回数据
if (@iCurPageNo > 1)
BEGIN
  SET @sWhereIn = N' AND m.ZID NOT IN(SELECT TOP ' + CAST(@iPageSize * (@iCurPageNo - 1) AS VARCHAR) + N' m.ZID FROM ' + @sFrom +  N' WHERE' + @sWhere + ' ORDER BY ' + @sOrder + ')'
  SET @SQLString = N'SELECT TOP ' +  CAST(@iPageSize AS VARCHAR) + @sSelect + N' FROM ' + @sFrom +  N' WHERE' + @sWhere + @sWhereIn + ' ORDER BY ' + @sOrder
END
else  -- 第一页
   SET @SQLString = N'SELECT TOP ' +  CAST(@iPageSize AS VARCHAR(8)) + @sSelect + N' FROM ' + @sFrom +  N' WHERE' + @sWhere + ' ORDER BY ' + @sOrder
 
print @SQLString

exec (@SQLString)

select RecordCount = @iRecordCount

go

 

--测试输入

declare @iRecordCount int
exec sp_GetPageData
N' m.*, g.ZNAME GroupName ',
N'TB_USER m LEFT JOIN TB_GROUP g ON m.ZGROUP_ID = g.ZID ',
N' 1=1',
N' m.ZID DESC',
10,
1,
@iRecordCount out

print 'iRecordCount = ' + CAST(@iRecordCount AS VARCHAR)