drop proc proc_generalSplit;
select * from tb_students;

-----------------------------------------------------最终版分页

create proc proc_generalSplit
@tableName varchar(50), --表名
@keyColumn varchar(30), --分页字段名（主键字段）
@condition varchar(100), --分页条件
@pageSize int, --每页显示的条数
@pageNum int, --显示第几页
@pageCount int output,
@count int output
as

declare @sql0 nvarchar(100);
set @sql0 = 'select @c=count(*) from '+@tableName;
if(@condition!='')begin
set @sql0 = @sql0 + ' where ' +@condition;
end
exec sp_executesql @sql0,N'@c int output',@count output;

 

if(@count%@pageSize=0)begin
set @pageCount=@count/@pageSize;
end else begin
set @pageCount=@count/@pageSize+1;
end

 

if(@pageNum<1)begin
set @pageNum=1;
end
if(@pageNum>@pageCount)begin
set @pageNum=@pageCount;
end

 

declare @sql varchar(400);
declare @m int;
set @m=@pageSize*(@pageNum-1);

set @sql = 'select top('+convert(varchar,@pageSize)+') * from '+@tableName+' where '+@keyColumn+' not in
(select top('+convert(varchar,@m)+') '+@keyColumn+' from '+@tableName;

if(@condition!='')begin
set @sql = @sql+' where '+@condition+') and '+@condition;
end else begin
set @sql = @sql+')';
end

exec(@sql)

go

--------------------------调用
declare
@pageCount int,
@count int;
begin
exec proc_generalSplit 'tb_students','stu_num','',5,1,@pageCount output,@count output;
print @pageCount;
print @count;
end