Sql分页查询解决方案

Sql分页查询

SQL code

/* QQ312430633 创建日期:2008-06-25 */ --修改了ORDER BY 需要 percent 与 ORDERBY 失效的BUG-----注意'roder by'用一个空格间隔Create PROCEDURE  [dbo].[les_AllowPaging] @pageindex int,    ----*****页码@PageSize int,     ----*****每页显示条数@tsql varchar(4000)----*****SQL语句asDeclare @SqlSelect  varchar(4000)Declare @orderby  varchar(4000)Declare @AllowPagingSql  varchar(4000) ---判断是否排序if CHARINDEX('order by',@tsql) <> 0begin    set @SqlSelect=replace(substring (@tsql,1, CHARINDEX('order by',@tsql)-1),'$','''')      set @orderby=replace(substring (@tsql, CHARINDEX('order by',@tsql),len(@tsql) ),'$','''')          set @AllowPagingSql=        'select * from (SELECT  ROW_NUMBER() OVER('+@orderby+') AS AllowPagingId,* FROM ('+        @SqlSelect        +') as table1) as table2 where AllowPagingId between '        +convert(varchar(10),((@pageindex-1) * @PageSize+1))+' and '        +convert(varchar(10), @pageindex * @PageSize)             exec  (@AllowPagingSql)end elsebegin    set @SqlSelect=replace(@tsql,'$','''')      set @orderby=''    set @AllowPagingSql=        'select *  from (SELECT  *,ROW_NUMBER() OVER(ORDER BY orderbyID DESC) AS AllowPagingId FROM  ( select *, 1 as orderbyID from ( '        +@SqlSelect        +' )  as  tbs1 )   as Tabl1 ) as table2 where AllowPagingId between '        +convert(varchar(10),((@pageindex-1) * @PageSize+1))+' and '        +convert(varchar(10), @pageindex * @PageSize)                 exec  (@AllowPagingSql)endset @AllowPagingSql='select case      when count(*)%'+convert(varchar(10),@PageSize)+'=0 then count(*)/'+convert(varchar(10),@PageSize)+'     when count(*)%'+convert(varchar(10),@PageSize)+'<>0 then count(*)/'+convert(varchar(10),@PageSize)+'+1end as pageCount,count(*) as RowsCount from ('+@SqlSelect+') as tab1'exec  (@AllowPagingSql)

[解决办法]
没办法的办法......

create PROCEDURE [dbo].[p_AllowPaging_Ex] 
@pageindex int, ----*****页码
@PageSize int, ----*****每页显示条数
@tsql varchar(4000),----*****SQL语句
@PageCount int output, ----返回页数
@RecCount int output--返回记录条数
as
/*
输入参数:@pageindexint,第@pageindex页
@PageSizeint,每页显示条数
@tsqlvarchar(4000) SQL语句

输出参数:@PageCountint总页数
@RecCountint总记录条数

功能:根据提交的sql语句,按照参数返回翻页查询的结果集.
*/
declare @tabname varchar(100),@dropsql varchar(1000),@execsql varchar(100)
--临时表表名
set @tabname = 'Temp'+replace(cast(host_name() as varchar),'-','')
set @dropsql='if exists (select * from sysobjects where name = '''+@tabname+''')
drop table '+@tabname
exec(@dropsql)
--将语句产生的结果集插入到新表@tabname中,并产生自动编号
select @tsql=left(@tsql,charindex('from',@tsql)-1)+',AllowPagingId=identity(int,1,1) into '+@tabname+' '+substring(@tsql,charindex('from',@tsql),len(@tsql)-charindex('from',@tsql)+1)
exec (@tsql)
--获得页数
declare @count int
set @count=@@rowcount
--获得记录数
set @RecCount=@count
select @PageCount = case when @count%@PageSize>0 then @count/@PageSize+1 else @count/@PageSize end
set @execsql = 'select * from '+@tabname+' where AllowPagingId between '+cast(@PageSize*@pageindex-@PageSize+1 as varchar)+' and '+cast(@PageSize*@pageindex as varchar)
exec (@execsql)
exec(@dropsql)

查看更多 下一篇