- 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([email protected]+') 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 ( ' [email protected] +' ) 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 ([email protected]+') 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
/*
输入参数:@pageindex int, [email protected]
@PageSize int, 每页显示条数
@tsql varchar(4000) SQL语句
输出参数:@PageCount int 总页数
@RecCount int 总记录条数
功能:根据提交的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 = [email protected]+''')
drop table [email protected]
exec(@dropsql)
[email protected],并产生自动编号
select @tsql=left(@tsql,charindex('from',@tsql)-1)+',AllowPagingId=identity(int,1,1) into [email protected]+' '+substring(@tsql,charindex('from',@tsql),len(@tsql)-charindex('from',@tsql)+1)
exec (@tsql)
--获得页数
declare @count int
set @count=@@rowcount
--获得记录数
set @[email protected]
select @PageCount = case when @[email protected]>0 then @[email protected]+1 else @[email protected] end
set @execsql = 'select * from [email protected]+' where AllowPagingId between '+cast(@PageSize*@pageindex-@PageSize+1 as varchar)+' and '+cast(@[email protected] as varchar)
exec (@execsql)
exec(@dropsql)