| 发表于：2007-03-22 00:05:291楼 得分:10 |
create procedure p_pagination
/*
***************************************************************
参数说明:
1.tables :表名称,视图
2.primarykey :主关键字
3.sort :排序语句，不带order by 比如：newsid desc,orderrows asc
4.currentpage :当前页码
5.pagesize :分页尺寸
6.filter :过滤语句，不带where
7.group :group语句,不带group by
***************************************************************/
（
@tables varchar（1000),
@primarykey varchar（100),
@sort varchar（200) = null,
@currentpage int = 1,
@pagesize int = 10,
@fields varchar（1000) = '* ',
@filter varchar（1000) = null,
@group varchar（1000) = null
)
as
/*默认排序*/
if @sort is null or @sort = ' '
set @sort = @primarykey
declare @sorttable varchar（100)
declare @sortname varchar（100)
declare @strsortcolumn varchar（200)
declare @operator char（2)
declare @type varchar（100)
declare @prec int
/*设定排序语句.*/
if charindex（ 'desc ',@sort)> 0
begin
set @strsortcolumn = replace（@sort, 'desc ', ' ')
end
else
begin
if charindex（ 'asc ', @sort) > 0
begin
set @strsortcolumn = replace（@sort, 'asc ', ' ')
end
end
if charindex（ '. ', @strsortcolumn) > 0
begin
set @sorttable = substring（@strsortcolumn, 0, charindex（ '. ',@strsortcolumn))
set @sortname = substring（@strsortcolumn, charindex（ '. ',@strsortcolumn) + 1, len（@strsortcolumn))
end
else
begin
set @sorttable = @tables
set @sortname = @strsortcolumn
end
select @type=t.name, @prec=c.prec
from sysobjects o
join syscolumns c on o.id=c.id
join systypes t on c.xusertype=t.xusertype
where o.name = @sorttable and c.name = @sortname
if charindex（ 'char ', @type) > 0
set @type = @type + '（ ' + cast（@prec as varchar) + ') '
declare @strpagesize varchar（50)
declare @strstartrow varchar（50)
declare @strfilter varchar（1000)
declare @strsimplefilter varchar（1000)
declare @strgroup varchar（1000)
/*默认当前页*/
if @currentpage < 1
set @currentpage = 1
/*设置分页参数.*/
set @strpagesize = cast（@pagesize as varchar（50))
set @strstartrow = cast（（（@currentpage - 1)*@pagesize) as varchar（50))
/*筛选以及分组语句.*/
if @filter is not null and @filter != ' '
begin
set @strfilter = ' where ' + @filter + ' '
set @strsimplefilter = @filter + ' '
end
else
begin
set @strsimplefilter = ' '
set @strfilter = ' '
end
if @group is not null and @group != ' '
set @strgroup = ' group by ' + @group + ' '
else
set @strgroup = ' '
ＥＸＥＣ（
'select top '+ @strpagesize+ ' * from '+@tables+ ' where '+@primarykey+ ' not in （ select top '+@strstartrow + ' '+@primarykey+ ' from '+ @tables + ' where ' + @strsimplefilter + ' ' + @strgroup + ' order by ' + @sort + ') and '+ @strsimplefilter + ' ' + @strgroup + ' order by ' + @sort
)
go | | |
|
