存储过程分页总结

表中主键必须为标识列，[ID] int IDENTITY (1,1)//每次自增一 

1.分页方案一：(利用Not In和SELECT TOP分页) 

语句形式： 

SELECT TOP 10 * 

FROM TestTable 

WHERE (ID NOT IN 

(SELECT TOP 20 id 

FROM TestTable 

ORDER BY id)) 

ORDER BY ID 

 

 

SELECT TOP 页大小 * 

FROM TestTable 

WHERE (ID NOT IN 

(SELECT TOP 页大小*(页数-1) id 

FROM 表 

ORDER BY id)) 

ORDER BY ID 

 

 

 

 

 

2.分页方案二：(利用ID大于多少和SELECT TOP分页） 

语句形式： 

SELECT TOP 10 * 

FROM TestTable 

WHERE (ID > 

(SELECT MAX(id) 

FROM (SELECT TOP 20 id 

FROM TestTable 

ORDER BY id) AS T)) 

ORDER BY ID 

 

 

SELECT TOP 页大小 * 

FROM TestTable 

WHERE ID > 

(SELECT MAX(id) 

FROM (SELECT TOP 页大小*(页数-1) id 

FROM 表 

ORDER BY id) AS T) 

ORDER BY ID 

 

 

 

 

 

 

3.分页方案三：(利用SQL的游标存储过程分页) 

 

create procedure SqlPager 

@sqlstr nvarchar(4000), --查询字符串 

@currentpage int, --第N页 

@pagesize int --每页行数 

as 

set nocount on 

declare @P1 int, --P1是游标的id 

@rowcount int 

exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1, @rowcount=@rowcount output 

select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页 

set @currentpage=(@currentpage-1)*@pagesize+1 

exec sp_cursorfetch @P1,16,@currentpage,@pagesize 

exec sp_cursorclose @P1 

set nocount off 

4.CREATE Procedure FramWorkPage 

@TableName varchar(50), --表名 

@Fields varchar(5000) = '*', --字段名(全部字段为*) 

@OrderField varchar(5000), --排序字段(必须!支持多字段) 

@sqlWhere varchar(5000) = Null,--条件语句(不用加where) 

@pageSize int, --每页多少条记录 

@pageIndex int = 1 , --指定当前为第几页 

@TotalPage int output, --返回条数 

@OrderType bit -- 设置排序类型,1 升序 0 值则降序 

as 

begin 

declare @strOrder varchar(400) -- 排序类型 

 

Begin Tran --开始事务 

Declare @sql nvarchar(4000); 

Declare @totalRecord int; 

--计算总记录数 

if (@SqlWhere ='''' or @SqlWhere='' or @sqlWhere is NULL) 

set @sql = 'select @totalRecord = count(*) from ' + @TableName 

else 

set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere 

EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数 

 

--计算总页数 

 

select @TotalPage=@totalRecord --CEILING((@totalRecord+0.0)/@PageSize) 

 

if @OrderType = 0 

begin 

set @strOrder = ' order by [' + @OrderField +'] desc' 

--如果@OrderType是0，就执行降序，这句很重要！ 

end 

else 

begin 

set @strOrder = ' order by [' + @OrderField +'] asc' 

end 

 

if (@SqlWhere ='''' or @SqlWhere='' or @sqlWhere is NULL) 

set @sql = 'Select * FROM (select ROW_NUMBER() Over( '+@strOrder+' ) as rowId,' + @Fields + ' from ' + @TableName 

else 

set @sql = 'Select * FROM (select ROW_NUMBER() Over( '+@strOrder+' ) as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere 

--处理页数超出范围情况 

if @PageIndex<=0 

Set @pageIndex = 1 

 

if @pageIndex>@TotalPage 

Set @pageIndex = @TotalPage 

 

--处理开始点和结束点 

Declare @StartRecord int 

Declare @EndRecord int 

 

set @StartRecord = (@pageIndex-1)*@PageSize + 1 

set @EndRecord = @StartRecord + @pageSize - 1 

 

if @OrderType = 0 

begin 

set @strOrder = ' order by rowid desc' 

--如果@OrderType是0，就执行降序，这句很重要！ 

end 

else 

begin 

set @strOrder = ' order by rowid asc' 

end 

--继续合成sql语句 

set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord) + ' '+@strOrder 

-- print @sql 

Exec(@Sql) 

--------------------------------------------------- 

If @@Error <> 0 

Begin 

RollBack Tran 

Return -1 

End 

Else 

Begin 

Commit Tran 

Return @totalRecord ---返回记录总数 

End 

end