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

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com