實用單表千萬級分頁儲存過程二（不敢獨享，特此分享）
實用單表千萬級分頁儲存過程二（不敢獨享，特此分享）
此儲存過程適合所有排序欄位，屬於通用型別
本儲存過程本人做了小修改
/**********************************************************************************************
********************通過指定的條件分頁查詢資料表【TableName
or
ViewName】記錄******************
**********************************************************************************************/
IF
EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id
(N'[dbo].[ThePaginationProcedureIsAllPurpose]') AND OBJECTPROPERTY(id,
N'IsProcedure') = 1)
DROP PROCEDURE
[dbo].[ThePaginationProcedureIsAllPurpose]
GO
--引數說明
-------------------------------------------------------------
/*
*
@tblName ----要顯示的表或多個表的連線
* @fldName ----要查詢出的欄位列表,*表示全部欄位
*
@pageSize ----每頁顯示的記錄個數
* @page ----要顯示那一頁的記錄
*
@fldSort ----排序欄位列表或條件，如：id desc (多個id desc,dt asc)
*
@Sort ----排序方法，0為升序，1為降序
* (如果是多欄位排列Sort指代最後一個排序欄位的排列順序(最後一個排序欄位
* 不加排序標記)--程式傳參如：'
SortA Asc,SortB Desc,SortC ')
* @strCondition ----查詢條件,不需where
*
@ID ----主表的主鍵
* @Dist ----是否新增查詢欄位的 DISTINCT 預設0不新增/1新增
*
@pageCount ----查詢結果分頁後的總頁數
* @Counts ----查詢到的總記錄數
*
@UsedTime ----耗時測試時間差
*/
CREATE PROCEDURE
[dbo].[ThePaginationProcedureIsAllPurpose]
(
@tblName
nvarchar(max),
@fldName nvarchar(max) = '*',
@pageSize int =
40,
@page int = 1,
@fldSort nvarchar(max) = null,
@Sort bit =
1,
@strCondition nvarchar(max) = null,
@ID nvarchar(max),
@Dist bit
= 0,
@pageCount int = 1 output,
@Counts int = 1 OUTPUT,
@UsedTime
int OUTPUT
)
AS
SET NOCOUNT ON
Declare @sqlTmp
nvarchar(max) ----存放動態生成的SQL語句
Declare @strTmp
nvarchar(max) ----存放取得查詢結果總數的查詢語句
Declare @strID
nvarchar(max) ----存放取得查詢開頭或結尾ID的查詢語句
Declare @strSortType
nvarchar(max) ----資料排序規則A
Declare @strFSortType nvarchar(max) ----資料排序規則B
Declare @SqlSelect nvarchar(max) ----對含有DISTINCT的查詢進行SQL構造
Declare
@SqlCounts nvarchar(max) ----對含有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 @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
if @page=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+' '+@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*(@page-1) as Varchar(20)) +' '+ @ID +' from
'+@tblName
+' order by '+ @fldSort +'
'+ @strFSortType+') AS TBMinID)'
+' order by '+ @fldSort +' '+
@strFSortType
end
end
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
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*(@page-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+' '+@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+' '+@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
@page = @pageIndex-@page+1 --後半部分資料處理
if @page <= 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+' '+@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
set @UsedTime =
datediff(ms,@timediff,getdate())
--select datediff(ms,@timediff,getdate()) as
耗時
--print @strTmp
SET NOCOUNT OFF
GO
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-616532/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 實用單表千萬級分頁儲存過程一（不敢獨享，特此分享）儲存過程
- 拋棄以往分頁方式，改用ROW_NUMBER()加BETWEEN方式的分頁儲存過程，特此分享儲存過程
- Sql儲存過程分頁--臨時表儲存SQL儲存過程
- 簡單的儲存過程分頁儲存過程
- 簡單的分頁儲存過程儲存過程
- 分頁儲存過程儲存過程
- SQL 分頁儲存過程SQL儲存過程
- 通用分頁儲存過程儲存過程
- sql儲存過程分頁SQL儲存過程
- sqlserver儲存過程實現多表分頁SQLServer儲存過程
- SqlServer-儲存過程分頁SQLServer儲存過程
- 使用儲存過程實現分頁列印 (轉)儲存過程
- oracle儲存過程分頁程式碼Oracle儲存過程
- (SQL Server)分頁的儲存過程SQLServer儲存過程
- 兩種SQL分頁方法儲存過程和遊標儲存過程SQL儲存過程
- 分頁控制元件及儲存過程控制元件儲存過程
- [MSSQL]mssql海量高效分頁儲存過程SQL儲存過程
- ORACLE高效分頁儲存過程程式碼Oracle儲存過程
- 基於ROWCOUNT的分頁儲存過程儲存過程
- SQLServer 2005通用分頁儲存過程SQLServer儲存過程
- 拋磚引玉——通用分頁儲存過程儲存過程
- SQL SERVER 2005分頁儲存過程SQLServer儲存過程
- 用儲存過程動態建立表儲存過程
- 簡單談基於SQL SERVER 分頁儲存過程的演進SQLServer儲存過程
- 儲存過程分頁 Ado.Net分頁 EF分頁 滿足90%以上儲存過程
- SQL Server 儲存過程的經典分頁（轉）SQLServer儲存過程
- asp.net利用儲存過程分頁程式碼ASP.NET儲存過程
- SqlServer儲存過程應用二：分頁查詢資料並動態拼接where條件SQLServer儲存過程
- MyBatis(八) 資料庫BLOB讀寫、批量更新操作、儲存過程呼叫、分表、分頁MyBatis資料庫儲存過程
- 完整的分頁儲存過程以及c#呼叫方法儲存過程C#
- 一個比較不錯的儲存過程分頁儲存過程
- [Procedure]Oracle之分頁儲存過程Oracle儲存過程
- 實戰儲存過程排程過程儲存過程
- 淺述asp.net海量分頁資料儲存過程ASP.NET儲存過程
- SQL Server 2000 的分頁查詢(儲存過程)SQLServer儲存過程
- asp.net分頁的SQL語句及儲存過程ASP.NETSQL儲存過程
- 用儲存過程封裝awrrpt指令碼（二）儲存過程封裝指令碼
- 在ORACLE裡用儲存過程定期分割表(轉)Oracle儲存過程