Tsql 存储过程的分页

use pubs
go
if exists(select name from sysobjects where name = 'emp')
drop table emp
go
--复制表
select emp_id,fname,lname into emp from dbo.employee

--select * from emp

--例如，求第3页，每页10条
--即第21到30条记录
 select top 10 * from emp
 where emp_id not in(select top 20 emp_id from emp order by emp_id)
 order by emp_id

--分页存储过程
if exists(select name from sysobjects where name = 'up_pager' and type ='p')
 drop procedure up_pager
go
create procedure up_pager
 @pageSize int,   --每页显示记录数
 @pageIndex int,   --当前页码
 @tableName varchar(30),  --表名
 @colName varchar(30)  --排序的字段名
as
begin
 declare @sqlStr varchar(300)
 if @pageIndex = 1
  set @sqlStr = ' select top '+cast(@pageSize as varchar(20))+' * from '+@tableName+
       ' order by '+@colName      
 else
  set @sqlStr = ' select top '+cast(@pageSize as varchar(20))+' * from '+@tableName+
       ' where '+@colName+' not in '+
       '(select top '+ cast((@pageIndex-1)*@pageSize as varchar(20)) +' '+@colName+' from '+@tableName+
       ' order by '+@colName+')'+
       ' order by '+@colName
 --print @sqlStr
 execute(@sqlStr)
end
go

execute up_pager 10,1,'emp','emp_id'

--例如求总记录数和总页数
select count(*) as 'rowcount','pageCount' = case
when count(*)% 10 = 0 then (count(*)/10)
when count(*)% 10 > 0 then (count(*)/10+1)
end
from emp

--求总记录数和总页数
if exists(select name from sysobjects where name = 'up_pagercount' and type ='p')
 drop procedure up_pagercount
go
create procedure up_pagercount
 @pageSize int,   --每页显示记录数
 @tableName varchar(30) --表名
as
begin
 declare @sqlStr varchar(300)
 set @sqlStr = 'select count(*) as ''RowCount'', pageCount = ' + ' case ' +
      ' when count(*)%'+cast(@pageSize as varchar(20)) +'=0 then count(*)/'+cast(@pageSize as varchar(20))+
      ' when count(*)%'+cast(@pageSize as varchar(20)) +'>0 then (count(*)/'+cast(@pageSize as varchar(20))+'+1)'+
     ' end' + ' from '+ @tableName
 execute (@sqlStr)
end
go

execute up_pagercount 10,'emp'