/* *************************************************************** ** 中国无忧商务网千万数量级分页存储过程 ** *************************************************************** 参数说明: 1.tables :表名称,视图 2.primarykey :主关键字 3.sort :排序语句，不带order by 比如：newsid desc,orderrows asc 4.currentpage :当前页码 5.pagesize :分页尺寸 6.filter :过滤语句，不带where 7.group :group语句,不带group by 版权所有：中国无忧商务网(http://www.cn5135.com) 欢迎试用，有什么心得请 m
create procedure cn5135_sp_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
版权所有：中国无忧商务网(http://www.cn5135.com)
欢迎试用，有什么心得请 mail:xzhijun@gmail.com
注：转载请保留相关版权说明，谢谢！^_^
***************************************************************/
(
@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’, ”)
set @operator = ‘<=’
end
else
begin
if charindex(‘asc’, @sort) = 0
set @strsortcolumn = replace(@sort, ‘asc’, ”)
set @operator = ‘>=’
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 + 1) as varchar(50))
/*筛选以及分组语句.*/
if @filter is not null and @filter != ”
begin
set @strfilter = ‘ where ‘ + @filter + ‘ ‘
set @strsimplefilter = ‘ and ‘ + @filter + ‘ ‘
end
else
begin
set @strsimplefilter = ”
set @strfilter = ”
end
if @group is not null and @group != ”
set @strgroup = ‘ group by ‘ + @group + ‘ ‘
else
set @strgroup = ”
/*执行查询语句*/
exec(
‘
declare @sortcolumn ‘ + @type + ‘
set rowcount ‘ + @strstartrow + ‘
select @sortcolumn=’ + @strsortcolumn + ‘ from ‘ + @tables + @strfilter + ‘ ‘ + @strgroup + ‘ order by ‘ + @sort + ‘
set rowcount ‘ + @strpagesize + ‘
select ‘ + @fields + ‘ from ‘ + @tables + ‘ where ‘ + @strsortcolumn + @operator + ‘ @sortcolumn ‘ + @strsimplefilter + ‘ ‘ + @strgroup + ‘ order by ‘ + @sort + ‘
‘
)
go