博客园-听涛榭·代码-最新评论

Re:URL编码表

金0501 — Wed, 28 Jul 2010 07:15:19 GMT

是什么东西啊

金0501 2010-07-28 15:15 发表评论

re: 简单快速实现分页的SQL查询语句

雨人 — Fri, 27 Jun 2008 15:46:58 GMT

你这条语句都有语法错误，根本谈不到实现分页
看看我这个存储过程使用够不够简单

只要页码，页显示最多条，和SQL语句
例如( exec [les_aaa] 4,15,' select * from sys.sysobjects )

/* QQ：312430633 创建日期:2008-6-28 Email:xinxiu_198471@163.com*/
CREATE PROCEDURE [dbo].[les_AllowPaging]
@pageindex int, ----*****页码
@PageSize int, ----*****每页显示条数
@tsql varchar(4000)----*****SQL语句
as

Declare @SqlSelect varchar(4000)
Declare @orderby varchar(4000)
Declare @AllowPagingSql varchar(4000)

---判断是否排序
if CHARINDEX('order by',@tsql) <> 0
begin
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
else
begin
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)
end

set @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)+'+1
end as pageCount,count(*) as RowsCount from ('+@SqlSelect+') as tab1'
exec (@AllowPagingSql)

--exec [les_aaa] 4,15,' select * from sys.sysobjects where id>$27$ '

雨人 2008-06-27 23:46 发表评论