【原创】-MS SQL2005 存储过程分页（简洁型）后续完善更新中......

alter proc p_pager(
@tableName varchar(50),--要应用分页的表
@keyName varchar(50),--主键
@sortName varchar(50),--排序字段
@pageSize int,--每页显示的数据条数
@pageCur  int,--当前页
@pageCount int output ,--总页数
@recordsCount int output--总数据条数
)
as
begin

 

declare @strSql NVarchar (200);
declare @strSql2 Nvarchar (200);
set @strSql2=N'select @recordsCount = count(1)'+N' FROM '+@tableName

EXEC sp_executesql @strSql2,N'@recordsCount int OUTPUT',@recordsCount OUTPUT

--select @pageCount=
--case
--when  @recordsCount%@pageSize =0 then @recordsCount/@pageSize
--else  @recordsCount/@pageSize+1
--end

set @pageCount= CEILING(@recordsCount*1.0/@pageSize);

set @strSql='select top '+ str(@pageSize)+' * from '+@tableName+' where '+@keyName+' not in (select top '+str(@pageSize*(@pageCur-1))+' '+
@keyName+' from '+@tableName+' order by '+@sortName+' ) order by '+@sortName;
exec (@strSql);

end