SQLServer 通用分页存储过程

 

 

/*

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)