USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[Sp_Pager] Script Date: 03/25/2013 17:54:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Sp_Pager]
@table varchar(100),--表名
@where varchar(200),--条件
@order varchar(200),--排序规则
@pagecount int,--每页的数量
@pageindex int,--当前页数
@totalpage int output,--总页数（输出）
@total int output--总人数（输出）
as
--定义全局变量
declare @sql nvarchar(500)
--确定主键
declare @primarykey varchar(100)=''
select @primarykey=name from sysobjects where parent_obj in(select id from sysobjects where name=@table) and xtype='PK'
--确定总人数
if @order !=NULL or @order !=''
begin
set @sql='select count(*) from '+@table+' '+@where+' order by '+@order
exec sp_executesql @sql,@total output
end
else
begin
set @sql='select count(*) from '+@table+' '+@where +' order by '+@primarykey
exec sp_executesql @sql,@total output
end
--确定总页数
set @totalpage=CEILING(@total/@pagecount)
--判断总页数与index的大小
if @order !=NULL or @order !=''
begin
if @pageindex=@totalpage
begin
set @sql=''
set @sql='select * from ('+'select * from '+@table+' '+@where+' order by '+@order +') where @primarykey not in ('+
'select top '+(@totalpage-1)*@pagecount+' '+@primarykey+' from '+@table+' '+@where+' order by '+@order +')'
exec sp_executesql @sql
end
else
begin
set @sql=''
set @sql='select * from ('+
'select top '+@pagecount+' * from(select top '+@pageindex*@pagecount+' * from '+@table+' order by '+@order+') a order by '+@primarykey+' DESC
) b order by '+@primarykey+' ASC'
end
end
else
begin
if @pageindex =@totalpage
begin
set @sql=''
set @sql='select * from ('+'select * from '+@table+' '+@where +' order by '+@primarykey +') where @primarykey not in ('+
'select top '+(@totalpage-1)*@pagecount+' '+@primarykey+' from '+@table+' '+@where +' order by '+@primarykey
exec sp_executesql @sql
end
else
begin
set @sql=''
set @sql='select * from ('+
'select top '+@pagecount+' * from(select top '+@pageindex*@pagecount+' * from '+@table+' order by '+@primarykey+'ASC ) a order by '+@primarykey+' DESC
) b order by '+@primarykey+' ASC'
end
end