sqlserver 千万数量级分页存储过程代码

set ansi_nulls on
set quoted_identifier on
go

create procedure [dbo].[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
***************************************************************/
(
@tables varchar(2000),
@primarykey varchar(500),
@sort varchar(500) = null,
@currentpage int = 1,
@pagesize int ,
@fields varchar(2000) = '*',
@filter varchar(1000) = null,
@group varchar(1000) = null
)
as
/**//*默认排序*/
if @sort is null or @sort = ''
set @sort = @primarykey
declare @sorttable varchar(1000)
declare @sortname varchar(1000)
declare @strsortcolumn varchar(1000)
declare @operator char(2)
declare @type varchar(1000)
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(500)
declare @strstartrow varchar(500)
declare @strfilter varchar(1000)
declare @strsimplefilter varchar(1000)
declare @strgroup varchar(1000)
/**//*默认当前页*/
if @currentpage < 1
set @currentpage = 1
/**//*设置分页参数.*/
set @strpagesize = cast(@pagesize as varchar(500))
set @strstartrow = cast(((@currentpage - 1)*@pagesize + 1) as varchar(500))
/**//*筛选以及分组语句.*/
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 + '
'
)