自己收集比较强大的分页存储过程 推荐
程序员文章站
2023-12-15 12:45:46
（下面的代码原来我想用折叠的代码的，但是在google里面老是添加不了折叠的代码，所以就整屏的贴出来了，望大家不要见外。） 朋友的比较好的存储过程。优点是：性能非常的高，每...
（下面的代码原来我想用折叠的代码的，但是在google里面老是添加不了折叠的代码，所以就整屏的贴出来了，望大家不要见外。）
朋友的比较好的存储过程。优点是：性能非常的高，每次查询都是根据id查询，每次都是对一半的数据进行分页。缺点是：当有多个排序条件时，分页数据显示会出现问题。（该问题在第二个网友的分页存储过程中有解决的方法）。
set ansi_nulls on
set quoted_identifier on
go
alter procedure [dbo].[proc_page]
(
@tblname nvarchar(200), ----要显示的表或多个表的连接lihu
@fldname nvarchar(500) = '*', ----要显示的字段列表
@pagesize int = 10, ----每页显示的记录个数
@page int = 1, ----要显示那一页的记录
@fldsort nvarchar(200) = null, ----排序字段列表或条件
@sort bit = 0, ----排序方法，0为升序，1为降序(如果是多字段排列sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' sorta asc,sortb desc,sortc ')
@strcondition nvarchar(1000), ----查询条件,不需where
@id nvarchar(150), ----主表的主键
@dist bit = 0, ----是否添加查询字段的 distinct 默认0不添加/1添加
@pagecount int = 1 output, ----查询结果分页后的总页数
@counts int = 1 output ----查询到的记录数
)
as
set nocount on
declare @sqltmp nvarchar(1000) ----存放动态生成的sql语句
declare @strtmp nvarchar(1000) ----存放取得查询结果总数的查询语句
declare @strid nvarchar(1000) ----存放取得查询开头或结尾id的查询语句
declare @strsorttype nvarchar(10) ----数据排序规则a
declare @strfsorttype nvarchar(10) ----数据排序规则b
declare @sqlselect nvarchar(50) ----对含有distinct的查询进行sql构造
declare @sqlcounts nvarchar(50) ----对含有distinct的总数查询进行sql构造
declare @timediff datetime --耗时测试时间差
select @timediff=getdate()
if @dist = 0
begin
set @sqlselect = 'select '
set @sqlcounts = 'count(*)'
end
else
begin
set @sqlselect = 'select distinct '
set @sqlcounts = 'count(distinct '+@id+')'
end
if @sort=0
begin
set @strfsorttype=' asc '
set @strsorttype=' desc '
end
else
begin
set @strfsorttype=' desc '
set @strsorttype=' asc '
end
--------生成查询语句--------
--此处@strtmp为取得查询结果数量的语句
if @strcondition is null or @strcondition='' --没有设置显示条件
begin
set @sqltmp = @fldname + ' from ' + @tblname
set @strtmp = @sqlselect+' @counts='+@sqlcounts+' from '+@tblname
set @strid = ' from ' + @tblname
end
else
begin
set @sqltmp = + @fldname + 'from ' + @tblname + ' where (1>0) ' + @strcondition
set @strtmp = @sqlselect+' @counts='+@sqlcounts+' from '+@tblname + ' where (1>0) ' + @strcondition
set @strid = ' from ' + @tblname + ' where (1>0) ' + @strcondition
end
----取得查询结果总数量-----
exec sp_executesql @strtmp,n'@counts int out ',@counts out
declare @tmpcounts int
if @counts = 0
set @tmpcounts = 1
else
set @tmpcounts = @counts
--取得分页总数
set @pagecount=(@tmpcounts+@pagesize-1)/@pagesize
/**当前页大于总页数 取最后一页**/
if 2>@pagecount
set 2=@pagecount
--/*-----数据分页2分处理-------*/
declare @pageindex int --总数/页大小
declare @lastcount int --总数%页大小
set @pageindex = @tmpcounts/@pagesize
set @lastcount = @tmpcounts%@pagesize
if @lastcount > 0
set @pageindex = @pageindex + 1
else
set @lastcount = @pagesize
--//***显示分页
if @strcondition is null or @strcondition='' --没有设置显示条件
begin
if @pageindex<2 or 2<=@pageindex / 2 + @pageindex % 2 --前半部分数据处理
begin
if 2=1
set @strtmp=@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fldname+' from '+@tblname
+' order by '+ @fldsort +' '+ @strfsorttype
else
begin
if @sort=1
begin
set @strtmp=@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fldname+' from '+@tblname
+' where '+@id+' <(select min('+ @id +') from ('+ @sqlselect+' top '+ cast(@pagesize*(2-1) as varchar(20)) +' '+ @id +' from '+@tblname
+' order by '+ @fldsort +' '+ @strfsorttype+') as tbminid)'
+' order by '+ @fldsort +' '+ @strfsorttype
end
else
begin
set @strtmp=@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fldname+' from '+@tblname
+' where '+@id+' >(select max('+ @id +') from ('+ @sqlselect+' top '+ cast(@pagesize*(2-1) as varchar(20)) +' '+ @id +' from '+@tblname
+' order by '+ @fldsort +' '+ @strfsorttype+') as tbminid)'
+' order by '+ @fldsort +' '+ @strfsorttype
end
end
end
else
begin
set 2= @pageindex-2+1 --后半部分数据处理
if 2<= 1 --最后一页数据显示
set @strtmp=@sqlselect+' * from ('+@sqlselect+' top '+ cast(@lastcount as varchar(4))+' '+ @fldname+' from '+@tblname
+' order by '+ @fldsort +' '+ @strsorttype+') as temptb'+' order by '+ @fldsort +' '+ @strfsorttype
else
if @sort=1
begin
set @strtmp=@sqlselect+' * from ('+@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fldname+' from '+@tblname
+' where '+@id+' >(select max('+ @id +') from('+ @sqlselect+' top '+ cast(@pagesize*(2-2)+@lastcount as varchar(20)) +' '+ @id +' from '+@tblname
+' order by '+ @fldsort +' '+ @strsorttype+') as tbmaxid)'
+' order by '+ @fldsort +' '+ @strsorttype+') as temptb'+' order by '+ @fldsort +' '+ @strfsorttype
end
else
begin
set @strtmp=@sqlselect+' * from ('+@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fldname+' from '+@tblname
+' where '+@id+' <(select min('+ @id +') from('+ @sqlselect+' top '+ cast(@pagesize*(2-2)+@lastcount as varchar(20)) +' '+ @id +' from '+@tblname
+' order by '+ @fldsort +' '+ @strsorttype+') as tbmaxid)'
+' order by '+ @fldsort +' '+ @strsorttype+') as temptb'+' order by '+ @fldsort +' '+ @strfsorttype
end
end
end
else --有查询条件
begin
if @pageindex<2 or @page<=@pageindex / 2 + @pageindex % 2 --前半部分数据处理
begin
if @page=1
set @strtmp=@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fldname+' from '+@tblname
+' where 1=1 ' + @strcondition + ' order by '+ @fldsort +' '+ @strfsorttype
else if(@sort=1)
begin
set @strtmp=@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fldname+' from '+@tblname
+' where '+@id+' <(select min('+ @id +') from ('+ @sqlselect+' top '+ cast(@pagesize*(@page-1) as varchar(20)) +' '+ @id +' from '+@tblname
+' where (1=1) ' + @strcondition +' order by '+ @fldsort +' '+ @strfsorttype+') as tbminid)'
+' '+ @strcondition +' order by '+ @fldsort +' '+ @strfsorttype
end
else
begin
set @strtmp=@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fldname+' from '+@tblname
+' where '+@id+' >(select max('+ @id +') from ('+ @sqlselect+' top '+ cast(@pagesize*(@page-1) as varchar(20)) +' '+ @id +' from '+@tblname
+' where (1=1) ' + @strcondition +' order by '+ @fldsort +' '+ @strfsorttype+') as tbminid)'
+' '+ @strcondition +' order by '+ @fldsort +' '+ @strfsorttype
end
end
else
begin
set 2= @pageindex-2+1 --后半部分数据处理
if 2<= 1 --最后一页数据显示
set @strtmp=@sqlselect+' * from ('+@sqlselect+' top '+ cast(@lastcount as varchar(4))+' '+ @fldname+' from '+@tblname
+' where (1=1) '+ @strcondition +' order by '+ @fldsort +' '+ @strsorttype+') as temptb'+' order by '+ @fldsort +' '+ @strfsorttype
else if(@sort=1)
set @strtmp=@sqlselect+' * from ('+@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fldname+' from '+@tblname
+' where '+@id+' >(select max('+ @id +') from('+ @sqlselect+' top '+ cast(@pagesize*(@page-2)+@lastcount as varchar(20)) +' '+ @id +' from '+@tblname
+' where (1=1) '+ @strcondition +' order by '+ @fldsort +' '+ @strsorttype+') as tbmaxid)'
+' '+ @strcondition+' order by '+ @fldsort +' '+ @strsorttype+') as temptb'+' order by '+ @fldsort +' '+ @strfsorttype
else
set @strtmp=@sqlselect+' * from ('+@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fldname+' from '+@tblname
+' where '+@id+' <(select min('+ @id +') from('+ @sqlselect+' top '+ cast(@pagesize*(@page-2)+@lastcount as varchar(20)) +' '+ @id +' from '+@tblname
+' where (1=1) '+ @strcondition +' order by '+ @fldsort +' '+ @strsorttype+') as tbmaxid)'
+' '+ @strcondition+' order by '+ @fldsort +' '+ @strsorttype+') as temptb'+' order by '+ @fldsort +' '+ @strfsorttype
end
end
------返回查询结果-----
exec sp_executesql @strtmp
select datediff(ms,@timediff,getdate()) as 耗时
print @strtmp
set nocount off
网上的比较经典的存储过程
create proc p_viewpage
/**//*
nzperfect [no_miss] 高效通用分页存储过程(双向检索) 2007.5.7 qq:34813284
敬告：适用于单一主键或存在唯一值列的表或视图
ps: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 < 1 or @sorttype >3
or @recordercount < 0 or @pagesize < 0 or @pageindex < 0
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 <= ceiling((@totalcount+0.0)/@pagesize)/2 --正向检索
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 <= ceiling((@totalcount+0.0)/@pagesize)/2 --正向检索
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 <= ceiling((@totalcount+0.0)/@pagesize)/2 --正向检索
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 < 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 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
朋友的比较好的存储过程。优点是：性能非常的高，每次查询都是根据id查询，每次都是对一半的数据进行分页。缺点是：当有多个排序条件时，分页数据显示会出现问题。（该问题在第二个网友的分页存储过程中有解决的方法）。
复制代码 代码如下:
set ansi_nulls on
set quoted_identifier on
go
alter procedure [dbo].[proc_page]
(
@tblname nvarchar(200), ----要显示的表或多个表的连接lihu
@fldname nvarchar(500) = '*', ----要显示的字段列表
@pagesize int = 10, ----每页显示的记录个数
@page int = 1, ----要显示那一页的记录
@fldsort nvarchar(200) = null, ----排序字段列表或条件
@sort bit = 0, ----排序方法，0为升序，1为降序(如果是多字段排列sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' sorta asc,sortb desc,sortc ')
@strcondition nvarchar(1000), ----查询条件,不需where
@id nvarchar(150), ----主表的主键
@dist bit = 0, ----是否添加查询字段的 distinct 默认0不添加/1添加
@pagecount int = 1 output, ----查询结果分页后的总页数
@counts int = 1 output ----查询到的记录数
)
as
set nocount on
declare @sqltmp nvarchar(1000) ----存放动态生成的sql语句
declare @strtmp nvarchar(1000) ----存放取得查询结果总数的查询语句
declare @strid nvarchar(1000) ----存放取得查询开头或结尾id的查询语句
declare @strsorttype nvarchar(10) ----数据排序规则a
declare @strfsorttype nvarchar(10) ----数据排序规则b
declare @sqlselect nvarchar(50) ----对含有distinct的查询进行sql构造
declare @sqlcounts nvarchar(50) ----对含有distinct的总数查询进行sql构造
declare @timediff datetime --耗时测试时间差
select @timediff=getdate()
if @dist = 0
begin
set @sqlselect = 'select '
set @sqlcounts = 'count(*)'
end
else
begin
set @sqlselect = 'select distinct '
set @sqlcounts = 'count(distinct '+@id+')'
end
if @sort=0
begin
set @strfsorttype=' asc '
set @strsorttype=' desc '
end
else
begin
set @strfsorttype=' desc '
set @strsorttype=' asc '
end
--------生成查询语句--------
--此处@strtmp为取得查询结果数量的语句
if @strcondition is null or @strcondition='' --没有设置显示条件
begin
set @sqltmp = @fldname + ' from ' + @tblname
set @strtmp = @sqlselect+' @counts='+@sqlcounts+' from '+@tblname
set @strid = ' from ' + @tblname
end
else
begin
set @sqltmp = + @fldname + 'from ' + @tblname + ' where (1>0) ' + @strcondition
set @strtmp = @sqlselect+' @counts='+@sqlcounts+' from '+@tblname + ' where (1>0) ' + @strcondition
set @strid = ' from ' + @tblname + ' where (1>0) ' + @strcondition
end
----取得查询结果总数量-----
exec sp_executesql @strtmp,n'@counts int out ',@counts out
declare @tmpcounts int
if @counts = 0
set @tmpcounts = 1
else
set @tmpcounts = @counts
--取得分页总数
set @pagecount=(@tmpcounts+@pagesize-1)/@pagesize
/**当前页大于总页数 取最后一页**/
if 2>@pagecount
set 2=@pagecount
--/*-----数据分页2分处理-------*/
declare @pageindex int --总数/页大小
declare @lastcount int --总数%页大小
set @pageindex = @tmpcounts/@pagesize
set @lastcount = @tmpcounts%@pagesize
if @lastcount > 0
set @pageindex = @pageindex + 1
else
set @lastcount = @pagesize
--//***显示分页
if @strcondition is null or @strcondition='' --没有设置显示条件
begin
if @pageindex<2 or 2<=@pageindex / 2 + @pageindex % 2 --前半部分数据处理
begin
if 2=1
set @strtmp=@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fldname+' from '+@tblname
+' order by '+ @fldsort +' '+ @strfsorttype
else
begin
if @sort=1
begin
set @strtmp=@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fldname+' from '+@tblname
+' where '+@id+' <(select min('+ @id +') from ('+ @sqlselect+' top '+ cast(@pagesize*(2-1) as varchar(20)) +' '+ @id +' from '+@tblname
+' order by '+ @fldsort +' '+ @strfsorttype+') as tbminid)'
+' order by '+ @fldsort +' '+ @strfsorttype
end
else
begin
set @strtmp=@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fldname+' from '+@tblname
+' where '+@id+' >(select max('+ @id +') from ('+ @sqlselect+' top '+ cast(@pagesize*(2-1) as varchar(20)) +' '+ @id +' from '+@tblname
+' order by '+ @fldsort +' '+ @strfsorttype+') as tbminid)'
+' order by '+ @fldsort +' '+ @strfsorttype
end
end
end
else
begin
set 2= @pageindex-2+1 --后半部分数据处理
if 2<= 1 --最后一页数据显示
set @strtmp=@sqlselect+' * from ('+@sqlselect+' top '+ cast(@lastcount as varchar(4))+' '+ @fldname+' from '+@tblname
+' order by '+ @fldsort +' '+ @strsorttype+') as temptb'+' order by '+ @fldsort +' '+ @strfsorttype
else
if @sort=1
begin
set @strtmp=@sqlselect+' * from ('+@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fldname+' from '+@tblname
+' where '+@id+' >(select max('+ @id +') from('+ @sqlselect+' top '+ cast(@pagesize*(2-2)+@lastcount as varchar(20)) +' '+ @id +' from '+@tblname
+' order by '+ @fldsort +' '+ @strsorttype+') as tbmaxid)'
+' order by '+ @fldsort +' '+ @strsorttype+') as temptb'+' order by '+ @fldsort +' '+ @strfsorttype
end
else
begin
set @strtmp=@sqlselect+' * from ('+@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fldname+' from '+@tblname
+' where '+@id+' <(select min('+ @id +') from('+ @sqlselect+' top '+ cast(@pagesize*(2-2)+@lastcount as varchar(20)) +' '+ @id +' from '+@tblname
+' order by '+ @fldsort +' '+ @strsorttype+') as tbmaxid)'
+' order by '+ @fldsort +' '+ @strsorttype+') as temptb'+' order by '+ @fldsort +' '+ @strfsorttype
end
end
end
else --有查询条件
begin
if @pageindex<2 or @page<=@pageindex / 2 + @pageindex % 2 --前半部分数据处理
begin
if @page=1
set @strtmp=@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fldname+' from '+@tblname
+' where 1=1 ' + @strcondition + ' order by '+ @fldsort +' '+ @strfsorttype
else if(@sort=1)
begin
set @strtmp=@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fldname+' from '+@tblname
+' where '+@id+' <(select min('+ @id +') from ('+ @sqlselect+' top '+ cast(@pagesize*(@page-1) as varchar(20)) +' '+ @id +' from '+@tblname
+' where (1=1) ' + @strcondition +' order by '+ @fldsort +' '+ @strfsorttype+') as tbminid)'
+' '+ @strcondition +' order by '+ @fldsort +' '+ @strfsorttype
end
else
begin
set @strtmp=@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fldname+' from '+@tblname
+' where '+@id+' >(select max('+ @id +') from ('+ @sqlselect+' top '+ cast(@pagesize*(@page-1) as varchar(20)) +' '+ @id +' from '+@tblname
+' where (1=1) ' + @strcondition +' order by '+ @fldsort +' '+ @strfsorttype+') as tbminid)'
+' '+ @strcondition +' order by '+ @fldsort +' '+ @strfsorttype
end
end
else
begin
set 2= @pageindex-2+1 --后半部分数据处理
if 2<= 1 --最后一页数据显示
set @strtmp=@sqlselect+' * from ('+@sqlselect+' top '+ cast(@lastcount as varchar(4))+' '+ @fldname+' from '+@tblname
+' where (1=1) '+ @strcondition +' order by '+ @fldsort +' '+ @strsorttype+') as temptb'+' order by '+ @fldsort +' '+ @strfsorttype
else if(@sort=1)
set @strtmp=@sqlselect+' * from ('+@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fldname+' from '+@tblname
+' where '+@id+' >(select max('+ @id +') from('+ @sqlselect+' top '+ cast(@pagesize*(@page-2)+@lastcount as varchar(20)) +' '+ @id +' from '+@tblname
+' where (1=1) '+ @strcondition +' order by '+ @fldsort +' '+ @strsorttype+') as tbmaxid)'
+' '+ @strcondition+' order by '+ @fldsort +' '+ @strsorttype+') as temptb'+' order by '+ @fldsort +' '+ @strfsorttype
else
set @strtmp=@sqlselect+' * from ('+@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fldname+' from '+@tblname
+' where '+@id+' <(select min('+ @id +') from('+ @sqlselect+' top '+ cast(@pagesize*(@page-2)+@lastcount as varchar(20)) +' '+ @id +' from '+@tblname
+' where (1=1) '+ @strcondition +' order by '+ @fldsort +' '+ @strsorttype+') as tbmaxid)'
+' '+ @strcondition+' order by '+ @fldsort +' '+ @strsorttype+') as temptb'+' order by '+ @fldsort +' '+ @strfsorttype
end
end
------返回查询结果-----
exec sp_executesql @strtmp
select datediff(ms,@timediff,getdate()) as 耗时
print @strtmp
set nocount off
网上的比较经典的存储过程
复制代码 代码如下:
create proc p_viewpage
/**//*
nzperfect [no_miss] 高效通用分页存储过程(双向检索) 2007.5.7 qq:34813284
敬告：适用于单一主键或存在唯一值列的表或视图
ps: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 < 1 or @sorttype >3
or @recordercount < 0 or @pagesize < 0 or @pageindex < 0
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 <= ceiling((@totalcount+0.0)/@pagesize)/2 --正向检索
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 <= ceiling((@totalcount+0.0)/@pagesize)/2 --正向检索
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 <= ceiling((@totalcount+0.0)/@pagesize)/2 --正向检索
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 < 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 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