mssqlsql高效分页方法代码

alter procedure dbo.proc_listpage
(
@tblname nvarchar(200), ----要显示的表或多个表的连接
@fldname nvarchar(500) = '*', ----要显示的字段列表
@pagesize int = 1, ----每页显示的记录个数
@page int = 10, ----要显示那一页的记录
@pagecount int = 1 output, ----查询结果分页后的总页数
@counts int = 1 output, ----查询到的记录数
@fldsort nvarchar(200) = null, ----排序字段列表或条件
@sort bit = 0, ----排序方法，0为升序，1为降序(如果是多字段排列sort指代最后

一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' sorta

asc,sortb desc,sortc ')
@strcondition nvarchar(1000) = null, ----查询条件,不需where
@id nvarchar(150), ----主表的主键
@dist bit = 0 ----是否添加查询字段的 distinct 默认0不添加/1添加
)
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构造

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 @page>@pagecount
set @page=@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 @page<=@pageindex / 2 + @pageindex % 2 --前半部分数据

处理
begin
set @strtmp=@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+

@fldname+' from '+@tblname
+' where '+@id+' not in('+ @sqlselect+' top '+ cast(@pagesize*(@page-1)

as varchar(20)) +' '+ @id +' from '+@tblname
+' order by '+ @fldsort +' '+ @strfsorttype+')'
+' order by '+ @fldsort +' '+ @strfsorttype
end
else
begin
set @page = @pageindex-@page+1 --后半部分数据处理
if @page <= 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
set @strtmp=@sqlselect+' * from ('+@sqlselect+' top '+ cast(@pagesize as

varchar(4))+' '+ @fldname+' from '+@tblname
+' where '+@id+' not in('+ @sqlselect+' top '+ cast(@pagesize*(@page-2)

+@lastcount as varchar(20)) +' '+ @id +' from '+@tblname
+' order by '+ @fldsort +' '+ @strsorttype+')'

+' order by '+ @fldsort +' '+ @strsorttype+') as temptb'+' order by '+

@fldsort +' '+ @strfsorttype
end
end

else --有查询条件
begin
if @pageindex<2 or @page<=@pageindex / 2 + @pageindex % 2 --前半部分数据

处理
begin
set @strtmp=@sqlselect+' top '+ cast(@pagesize as varchar(4))+' '+

@fldname +' from '+@tblname
+' where '+@id+' not in('+ @sqlselect+' top '+ cast(@pagesize*(@page-1)

as varchar(20)) +' '+ @id +' from '+@tblname
+' where (1>0) ' + @strcondition + ' order by '+ @fldsort +' '+

@strfsorttype+')'
+' ' + @strcondition + ' order by '+ @fldsort +' '+ @strfsorttype
end
else
begin
set @page = @pageindex-@page+1 --后半部分数据处理
if @page <= 1 --最后一页数据显示
set @strtmp=@sqlselect+' * from ('+@sqlselect+' top '+ cast(@lastcount as

varchar(4))+' '+ @fldname+' from '+@tblname
+' where (1>0) '+ @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+' not in('+ @sqlselect+' top '+ cast(@pagesize*(@page-2)

+@lastcount as varchar(20)) +' '+ @id +' from '+@tblname
+' where (1>0) '+ @strcondition +' order by '+ @fldsort +' '+

@strsorttype+')'
+ @strcondition +' order by '+ @fldsort +' '+ @strsorttype+') as

temptb'+' order by '+ @fldsort +' '+ @strfsorttype
end
end

------返回查询结果-----
exec sp_executesql @strtmp
--print @strtmp
set nocount off