代码如下 复制代码
create proc p_viewpage
/**//*
nzperfect [no_miss] 高效通用存储过程(双向检索) 2007.5.7 qq:34813284
敬告：适用于单一主键或存在唯一值列的表或视图
:sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围
*/
@tablename varchar(200), --表名
@fieldlist varchar(2000), --显示列名，如果是全部字段则为*
@primarykey varchar(100), --单一主键或唯一值键
@where varchar(2000), --查询条件 不含'where'字符，如id>10 and len(userid)>9
@order varchar(1000), --排序 不含'order by'字符，如id asc,userid desc，必须指定asc或desc
--注意当@sorttype=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷
@sorttype int, --排序规则 1:正序asc 2:倒序desc 3:多列排序方法
@recordercount int, --记录总数 0:会返回总记录
@pagesize int, --每页输出的记录数
@pageindex int, --当前页数
@totalcount int output , --记返回总记录
@totalpagecount int output --返回总页数
as
set nocount on
if isnull(@totalcount,'') = '' set @totalcount = 0
set @order = rtrim(ltrim(@order))
set @primarykey = rtrim(ltrim(@primarykey))
set @fieldlist = replace(rtrim(ltrim(@fieldlist)),' ','')
while charindex(', ',@order) > 0 or charindex(' ,',@order) > 0
begin
set @order = replace(@order,', ',',')
set @order = replace(@order,' ,',',')
end
if isnull(@tablename,'') = '' or isnull(@fieldlist,'') = ''
or isnull(@primarykey,'') = ''
or @sorttype 3
or @recordercount begin
print('err_00')
return
end
if @sorttype = 3
begin
if (upper(right(@order,4))!=' asc' and upper(right(@order,5))!=' desc')
begin print('err_02') return end
end
declare @new_where1 varchar(1000)
declare @new_where2 varchar(1000)
declare @new_order1 varchar(1000)
declare @new_order2 varchar(1000)
declare @new_order3 varchar(1000)
declare @sql varchar(8000)
declare @sqlcount nvarchar(4000)
if isnull(@where,'') = ''
begin
set @new_where1 = ' '
set @new_where2 = ' where '
end
else
begin
set @new_where1 = ' where ' + @where
set @new_where2 = ' where ' + @where + ' and '
end
if isnull(@order,'') = '' or @sorttype = 1 or @sorttype = 2
begin
if @sorttype = 1
begin
set @new_order1 = ' order by ' + @primarykey + ' asc'
set @new_order2 = ' order by ' + @primarykey + ' desc'
end
if @sorttype = 2
begin
set @new_order1 = ' order by ' + @primarykey + ' desc'
set @new_order2 = ' order by ' + @primarykey + ' asc'
end
end
else
begin
set @new_order1 = ' order by ' + @order
end
if @sorttype = 3 and charindex(','+@primarykey+' ',','+@order)>0
begin
set @new_order1 = ' order by ' + @order
set @new_order2 = @order + ','
set @new_order2 = replace(replace(@new_order2,'asc,','{asc},'),'desc,','{desc},')
set @new_order2 = replace(replace(@new_order2,'{asc},','desc,'),'{desc},','asc,')
set @new_order2 = ' order by ' + substring(@new_order2,1,len(@new_order2)-1)
if @fieldlist '*'
begin
set @new_order3 = replace(replace(@order + ',','asc,',','),'desc,',',')
set @fieldlist = ',' + @fieldlist
while charindex(',',@new_order3)>0
begin
if charindex(substring(','+@new_order3,1,charindex(',',@new_order3)),','+@fieldlist+',')>0
begin
set @fieldlist =
@fieldlist + ',' + substring(@new_order3,1,charindex(',',@new_order3))
end
set @new_order3 =
substring(@new_order3,charindex(',',@new_order3)+1,len(@new_order3))
end
set @fieldlist = substring(@fieldlist,2,len(@fieldlist))
end
end
set @sqlcount = 'select @totalcount=count(*),@totalpagecount=ceiling((count(*)+0.0)/'
+ cast(@pagesize as varchar)+') from (select * from ' + @tablename + @new_where1+') as t'
if @recordercount = 0
begin
exec sp_executesql @sqlcount,n'@totalcount int output,@totalpagecount int output',
@totalcount output,@totalpagecount output
end
else
begin
select @totalcount = @recordercount
end
if @pageindex > ceiling((@totalcount+0.0)/@pagesize)
begin
set @pageindex = ceiling((@totalcount+0.0)/@pagesize)
end
if @pageindex = 1 or @pageindex >= ceiling((@totalcount+0.0)/@pagesize)
begin
if @pageindex = 1 --返回第一页数据
begin
set @sql = 'select * from (select top ' + str(@pagesize) + ' ' + @fieldlist + ' from '
+ @tablename + @new_where1 + @new_order1 +') as tmp ' + @new_order1
end
if @pageindex >= ceiling((@totalcount+0.0)/@pagesize) --返回最后一页数据
begin
set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from ('
+ 'select top ' + str(abs(@pagesize*@pageindex-@totalcount-@pagesize))
+ ' ' + @fieldlist + ' from '
+ @tablename + @new_where1 + @new_order2 + ' ) as tmp '
+ @new_order1
end
end
else
begin
if @sorttype = 1 --仅主键正序排序
begin
if @pageindex begin
set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from '
+ @tablename + @new_where2 + @primarykey + ' > '
+ '(select max(' + @primarykey + ') from (select top '
+ str(@pagesize*(@pageindex-1)) + ' ' + @primarykey
+ ' from ' + @tablename
+ @new_where1 + @new_order1 +' ) as tmp) '+ @new_order1
end
else --反向检索
begin
set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from ('
+ 'select top ' + str(@pagesize) + ' '
+ @fieldlist + ' from '
+ @tablename + @new_where2 + @primarykey + ' + '(select min(' + @primarykey + ') from (select top '
+ str(@totalcount-@pagesize*@pageindex) + ' ' + @primarykey
+ ' from ' + @tablename
+ @new_where1 + @new_order2 +' ) as tmp) '+ @new_order2
+ ' ) as tmp ' + @new_order1
end
end
if @sorttype = 2 --仅主键反序排序
begin
if @pageindex begin
set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from '
+ @tablename + @new_where2 + @primarykey + ' + '(select min(' + @primarykey + ') from (select top '
+ str(@pagesize*(@pageindex-1)) + ' ' + @primarykey
+' from '+ @tablename
+ @new_where1 + @new_order1 + ') as tmp) '+ @new_order1
end
else --反向检索
begin
set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from ('
+ 'select top ' + str(@pagesize) + ' '
+ @fieldlist + ' from '
+ @tablename + @new_where2 + @primarykey + ' > '
+ '(select max(' + @primarykey + ') from (select top '
+ str(@totalcount-@pagesize*@pageindex) + ' ' + @primarykey
+ ' from ' + @tablename
+ @new_where1 + @new_order2 +' ) as tmp) '+ @new_order2
+ ' ) as tmp ' + @new_order1
end
end
if @sorttype = 3 --多列排序，必须包含主键，且放置最后，否则不处理
begin
if charindex(',' + @primarykey + ' ',',' + @order) = 0
begin print('err_02') return end
if @pageindex begin
set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from ( '
+ 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from ( '
+ ' select top ' + str(@pagesize*@pageindex) + ' ' + @fieldlist
+ ' from ' + @tablename + @new_where1 + @new_order1 + ' ) as tmp '
+ @new_order2 + ' ) as tmp ' + @new_order1
end
else --反向检索
begin
set @sql = 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from ( '
+ 'select top ' + str(@pagesize) + ' ' + @fieldlist + ' from ( '
+ ' select top ' + str(@totalcount-@pagesize *@pageindex+@pagesize) + ' ' + @fieldlist
+ ' from ' + @tablename + @new_where1 + @new_order2 + ' ) as tmp '
+ @new_order1 + ' ) as tmp ' + @new_order1
end
end
end
print(@sql)
exec(@sql)
自己写的一个
代码如下 复制代码
use [caili]
go
/****** object: storedprocedure [dbo].[sqlpagination] script date: 10/26/2011 11:40:46 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create procedure [dbo].[sqlpagination]
/*
***************************************************************
** 千万数量级分页存储过程 **
***************************************************************
参数说明:
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 @primarykey is null or @primarykey = ''
set @primarykey='id'
if @sort is null or @sort = ''
set @sort = @primarykey
if @fields is null or @fields = ''
set @fields = '*'
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(',',@sort) >0
set @strsortcolumn = substring(@sort,0,charindex(',',@sort))
else
set @strsortcolumn = @sort
if charindex('desc',@sort)>0
begin
set @strsortcolumn = replace(@strsortcolumn, 'desc', '')
set @operator = '
end
else
begin
if charindex('asc',@sort)> 0
begin
set @strsortcolumn = replace(@strsortcolumn, 'asc', '')
set @operator = '>='
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)
declare @strsort varchar(200)
/*默认当前页*/
if @currentpage
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 1=1 ' + @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 = ''
if @sort is not null and @sort != ''
set @strsort = ' order by ' + @sort + ' '
else
set @strsort = ''
--print('select ' + @fields + ' from ' + '(select *,row_number() over ('+@strsort+')as rownumber from '+@tables+') t' + ' where t.rownumber between '+@strstartrow+' and '+' ' + @strsimplefilter + ' ' + @strsort + @strgroup)
/*执行查询语句*/
declare @strorder varchar(50)
if charindex(',',@strsort)>0
set @strorder=substring(@strsort, 0, charindex(',',@strsort))
else
set @strorder=@strsort
exec(
' declare @sortcolumn ' + @type + '
declare @totalcount int
declare @endcount int
declare @strendcount varchar(50)
--select count(1) from ' + @tables + @strfilter+'
set @totalcount=(select count(1) from ' + @tables + @strfilter+')'+'
set rowcount ' + @strstartrow + '
set @endcount=cast('+@strstartrow+' as int)+cast('+@strpagesize+' as int)-1
if @endcount > @totalcount
begin
set @endcount = @totalcount
end
set @strendcount=cast(@endcount as varchar(50))
select @sortcolumn=' + @strsortcolumn + ' from ' + @tables + @strfilter + ' ' + @strgroup + @strsort + '
set rowcount ' + @strpagesize + '
select ' + @fields + ' from ' + '(select *,row_number() over ('+@strorder+')as rownumber from '+@tables+' where 1=1 '+@strsimplefilter+') t' + ' where t.rownumber between '+@strstartrow+' and @strendcount ' + @strgroup + @strsort + ' ')
go