/*
declare @n int
exec sp_fenye 'select * from baseinfo','id desc','id',3,2,@count=@n output
select @n
*/
-- =============================================
-- Author: shipeng.wang
-- Create date: 2010-04-27
-- Description: 分页存储过程(对结果集进行分页)
-- =============================================
create proc [dbo].[sp_fenye]
@sql varchar(2000), --要分页的结果集
@orders varchar(50),--要排序的字段(dt,)
@keyfield varchar(20),
@pageindex int,
@pagesize int,
@count int output
as
if(@pageindex=1)
begin
--获取到总记录数量
declare @nsql nvarchar(4000)
set @nsql='select @ct=count(*) from ('+@sql+')a'
exec sp_executesql @nsql,N'@ct int output',@count output
end
--获取当前页数据
set @nsql='select top '+ltrim(@pagesize)+' * from ('+@sql+')a
where '+@keyfield+' not in (select top '+ltrim((@pageindex-1)*@pagesize)+' '+@keyfield+' from ('+@sql+')a order by '+@orders+')
order by '+@orders
exec(@nsql)