通用分页存储过程

分享--我用以上代码修改的兼容网上流行的"通用分页存储过程" 2005-09-14 18:08 | 木木

CREATE PROCEDURE pagination
(
@tablename varchar(255), --表名或视图表
@fieldlist varchar(1000)='*', --欲选择字段列表
@orderfield varchar(255)='', --排序字段
@pagesize int=10, --页尺寸
@pageindex int=1, --页号
@docount bit=0, --返回记录总数, 非 0 值则返回
@ordertype bit=0, --排序,1,降序,0,升序
@strwhere varchar(1500)='' -- 查询条件 (注意: 不要加 where)
)
AS
/*
名称:GetPagingRecord
作用:按任意字段进行排序分页
作者:菩提树(MARK MA)
时间:2004-12-14
声明:此代码你可以无偿使用及转载,但在转载时,请勿移称本文字声明
修改：木木(mumuqz) 2005-09-14
*/
SET NOCOUNT ON
declare @sqlstr varchar(6000)
set @sqlstr='declare @rcount int;'

if @doCount != 0 -----------------------------------------------统计总记录数
begin
if @strwhere !=''
begin
set @sqlstr = 'select count(*) as Total from ' + @tablename + ' where '+@strwhere
set @strwhere=replace(@strwhere,'''','''''') ------处理SQL中危险字符,并且将条件处理成易嵌入的形式
set @strwhere=replace(@strwhere,'--','')
set @strwhere=replace(@strwhere,';','')
end
else
begin
set @sqlstr = 'select count(*) as Total from ' + @tablename
end
end
else -----------------------------------------------------------分页显示记录
begin
if @strwhere !='' ----------------------------------------当查询条件不为空
begin
set @sqlstr=@sqlstr+'set @rcount=(select count(*) from '+@tablename+' where '+@strwhere+');'
set @strwhere=replace(@strwhere,'''','''''')
set @strwhere=replace(@strwhere,'--','')
set @strwhere=replace(@strwhere,';','')
set @sqlstr=@sqlstr+'declare @rnum int;'
set @sqlstr=@sqlstr+'set @rnum=@rcount-'+cast(@pagesize as varchar)+'*('+cast(@pageindex as varchar)+'-1);'
set @sqlstr=@sqlstr+'declare @sqlstr varchar(6000);'
if @ordertype=1
begin
set @sqlstr=@sqlstr+'set @sqlstr=''select top '+cast(@Pagesize as varchar)+' '+@fieldlist+' from (select top 100 percent * from (select top ''+cast(@rnum as varchar)+'' * from '+@tablename+' where '+@strwhere+' order by '+@orderfield+' asc) as b order by '+@orderfield+' desc) as a order by '+@orderfield+' desc '';'
end
else
begin
set @sqlstr=@sqlstr+'set @sqlstr=''select top '+cast(@Pagesize as varchar)+' '+@fieldlist+' from (select top 100 percent * from (select top ''+cast(@rnum as varchar)+'' * from '+@tablename+' where '+@strwhere+' order by '+@orderfield+' desc) as b order by '+@orderfield+' asc) as a order by '+@orderfield+' asc '';'
end
set @sqlstr=@sqlstr+'execute(@sqlstr)'
end
else --------------------------------------------------------------当查询条件为空
begin
set @sqlstr=@sqlstr+'set @rcount=(select count(*) from '+@tablename+');'
set @sqlstr=@sqlstr+'declare @rnum int;'
set @sqlstr=@sqlstr+'set @rnum=@rcount-'+cast(@pagesize as varchar)+'*('+cast(@pageindex as varchar)+'-1);'
set @sqlstr=@sqlstr+'declare @sqlstr varchar(6000);'
if @ordertype=1
begin
set @sqlstr=@sqlstr+'set @sqlstr=''select top '+cast(@Pagesize as varchar)+' '+@fieldlist+' from (select top 100 percent * from (select top ''+cast(@rnum as varchar)+'' * from '+@tablename+' order by '+@orderfield+' asc) as b order by '+@orderfield+' desc) as a order by '+@orderfield+' desc '';'
end
else
begin
set @sqlstr=@sqlstr+'set @sqlstr=''select top '+cast(@Pagesize as varchar)+' '+@fieldlist+' from (select top 100 percent * from (select top ''+cast(@rnum as varchar)+'' * from '+@tablename+' order by '+@orderfield+' desc) as b order by '+@orderfield+' asc) as a order by '+@orderfield+' asc '';'
end
set @sqlstr=@sqlstr+'execute(@sqlstr)'
end
end
--print @sqlstr
execute(@sqlstr)
GO