【乾貨】SqlServer 總結幾種存儲過程分頁的使用

来源:https://www.cnblogs.com/xiongze520/archive/2019/05/13/10855312.html
-Advertisement-
Play Games

就我而言寫代碼最煩的就是處理數據，其中之一就是分頁的使用。 有的代碼寫多了，總結出一套適用自己的分頁方法；有的查一下資料借鑒一下套用起來也達到目的。 那麼小編在這裡給大家總結幾個方法供大家做一下參考。 分頁方法一： 方法二： 方法三： 方式四： 還有更多分頁方式，感興趣的可以去查查資料，上面的分頁方 ...


就我而言寫代碼最煩的就是處理數據，其中之一就是分頁的使用。

有的代碼寫多了，總結出一套適用自己的分頁方法；有的查一下資料借鑒一下套用起來也達到目的。

那麼小編在這裡給大家總結幾個方法供大家做一下參考。

分頁方法一：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO/*********************************************************  
* 作    用：數據分頁
* 作    者：Ozawa
* 作者博客：https://www.cnblogs.com/xiongze520/
* 創建日期：2019-05-13
* 使用說明：
    --調用例子：
    --1.單表/單排序
    EXEC proc_DataPagination @TableNames='bigtable',@PrimaryKey='d_id',@Fields='d_id,d_title,d_content,d_time',@PageSize=20,@CurrentPage=1,@Filter ='',@Group='',@Order='d_id desc'
    --2.單表/多排序
    EXEC proc_DataPagination 'bigtable','d_id','*',20,0,'','','d_time asc,d_id desc'
    --3.多表/單排序
    EXEC proc_DataPagination 'bigtable left join bigtable_author on bigtable.d_id=bigtable_author.BigTable_id', 'bigtable.d_id', 'bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author', 20, 0, '', '', 'bigtable.d_id asc'
    --4.多表/多排序
    EXEC proc_DataPagination 'bigtable left join bigtable_author on bigtable.d_id=bigtable_author.BigTable_id', 'bigtable.d_id', 'bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author', 20, 0, '', '', 'bigtable.d_time asc,bigtable.d_id desc'

*********************************************************/  
 CREATE PROCEDURE [dbo].[proc_DataPagination]  
@TableNames VARCHAR(200),    --表名，可以是多個表，但不能用別名
@PrimaryKey VARCHAR(100),    --主鍵，可以為空，但@Order為空時該值不能為空
@Fields    VARCHAR(200),        --要取出的欄位，可以是多個表的欄位，可以為空，為空表示select *
@PageSize INT,            --每頁記錄數
@CurrentPage INT,        --當前頁，0表示第1頁
@Filter VARCHAR(200) = '',    --條件，可以為空，不用填 where
@Group VARCHAR(200) = '',    --分組依據，可以為空，不用填 group by
@Order VARCHAR(200) = ''    --排序，可以為空，為空預設按主鍵升序排列，不用填 order by
AS
BEGIN
    DECLARE @SortColumn VARCHAR(200)
    DECLARE @Operator CHAR(2)
    DECLARE @SortTable VARCHAR(200)
    DECLARE @SortName VARCHAR(200)
    IF @Fields = ''
        SET @Fields = '*'
    IF @Filter = ''
        SET @Filter = 'WHERE 1=1'
    ELSE
        SET @Filter = 'WHERE ' +  @Filter
    IF @Group <>''
        SET @Group = 'GROUP BY ' + @Group

    IF @Order <> ''
    BEGIN
        DECLARE @pos1 INT, @pos2 INT
        SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')
        IF CHARINDEX(' DESC', @Order) > 0
            IF CHARINDEX(' ASC', @Order) > 0
            BEGIN
                IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)
                    SET @Operator = '<='
                ELSE
                    SET @Operator = '>='
            END
            ELSE
                SET @Operator = '<='
        ELSE
            SET @Operator = '>='
        SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')
        SET @pos1 = CHARINDEX(',', @SortColumn)
        IF @pos1 > 0
            SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1)
        SET @pos2 = CHARINDEX('.', @SortColumn)
        IF @pos2 > 0
        BEGIN
            SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)
            IF @pos1 > 0 
                SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1)
            ELSE
                SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)
        END
        ELSE
        BEGIN
            SET @SortTable = @TableNames
            SET @SortName = @SortColumn
        END
    END
    ELSE
    BEGIN
        SET @SortColumn = @PrimaryKey
        SET @SortTable = @TableNames
        SET @SortName = @SortColumn
        SET @Order = @SortColumn
        SET @Operator = '>='
    END

    DECLARE @type varchar(50)
    DECLARE @prec int
    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 @TopRows INT
    SET @TopRows = @PageSize * @CurrentPage + 1
    print @TopRows
    print @Operator
    EXEC('
        DECLARE @SortColumnBegin ' + @type + '
        SET ROWCOUNT ' + @TopRows + '
        SELECT @SortColumnBegin=' + @SortColumn + ' FROM  ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
        SET ROWCOUNT ' + @PageSize + '
        SELECT ' + @Fields + ' FROM  ' + @TableNames + ' ' + @Filter  + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + '    
    ')    
END

GO

 

方法二：

/*********************************************************  
* 作    用：數據分頁
* 作    者：Ozawa
* 作者博客：https://www.cnblogs.com/xiongze520/
* 創建日期：2019-05-13
* 使用說明：
　　　　　　declare @pageCount int 
　　　　　　exec commonPagination 　'job_id,job_desc','jobs','job_id', 'asc','1=1',2,2,@pageCount output 
　　　　　　--select '總頁數為：' + str(@pageCount)
*********************************************************/

CREATE  PROCEDURE commonPagination
@columns varchar(500), --要顯示的列名，用逗號隔開 
@tableName varchar(100), --要查詢的表名 
@orderColumnName varchar(100), --排序的列名 
@order varchar(50), --排序的方式，升序為asc,降序為 desc 
@where varchar(100), --where 條件，如果不帶查詢條件，請用 1=1 
@pageIndex int, --當前頁索引 
@pageSize int, --頁大小(每頁顯示的記錄條數) 
@pageCount int  --總頁數，輸出參數 
as 
begin 
    declare @sqlRecordCount nvarchar(1000) --得到總記錄條數的語句 
    declare @sqlSelect nvarchar(1000) --查詢語句 
    set @sqlRecordCount=N'select @recordCount=count(*) from ' +@tableName + ' where '+ @where 
    
    declare @recordCount int --保存總記錄條數的變數 
    exec sp_executesql @sqlRecordCount,N'@recordCount int output',@recordCount output 
    
    --動態 sql 傳參 
    if( @recordCount % @pageSize = 0) --如果總記錄條數可以被頁大小整除 
        set @pageCount = @recordCount / @pageSize --總頁數就等於總記錄條數除以頁大小 
    else --如果總記錄條數不能被頁大小整除 
        set @pageCount = @recordCount / @pageSize + 1 --總頁數就等於總記錄條數除以頁大小加1 
    
    set @sqlSelect = 
    N'select '+@columns+' from ( 
    select row_number() over (order by ' 
    +@orderColumnName+' '+@order 
    +') as tempid,* from ' 
    +@tableName+' where '+ @where 
    +') as tempTableName where tempid between ' 
    +str((@pageIndex - 1)*@pageSize + 1 ) 
    +' and '+str( @pageIndex * @pageSize) 
    exec (@sqlSelect) --執行動態Sql 
end

 

方法三：

create PROCEDURE commonPagination
(@TableName   VARCHAR(2000),         --表名
 @ReFieldsStr VARCHAR(1000) = '*',     --欄位名(全部欄位為*)
 @OrderString VARCHAR(200),         --排序欄位(必須!支持多欄位不用加order by)
 @WhereString VARCHAR(500) = N'',     --條件語句(不用加where)
 @PageSize    INT,                     --每頁多少條記錄
 @PageIndex   INT          = 1,     --指定當前為第幾頁
 @TotalRecord INT OUTPUT            --返回總記錄數
)
AS
     BEGIN    
    --處理開始點和結束點
         DECLARE @StartRecord INT;
         DECLARE @EndRecord INT;
         DECLARE @TotalCountSql NVARCHAR(500);
         DECLARE @SqlString NVARCHAR(2000);
         SET @StartRecord = (@PageIndex - 1) * @PageSize + 1;
         SET @EndRecord = @StartRecord + @PageSize - 1;
         SET @TotalCountSql = N'select @TotalRecord = count(*) from '+@TableName;--總記錄數語句
         SET @SqlString = N'(select row_number() over (order by '+@OrderString+') as rowId,'+@ReFieldsStr+' from '+@TableName;--查詢語句
    -- 判斷條件是否為空
         IF(@WhereString != ''
            OR @WhereString != NULL)
             BEGIN
                 SET @TotalCountSql = @TotalCountSql+'  where '+@WhereString;
                 SET @SqlString = @SqlString+'  where '+@WhereString;
         END;
    --返回總記錄數
         EXEC sp_executesql
              @totalCountSql,
              N'@TotalRecord int out',
              @TotalRecord OUTPUT;
    ----執行主語句
         SET @SqlString = 'select * from '+@SqlString+') as t where rowId between '+LTRIM(STR(@StartRecord))+' and '+LTRIM(STR(@EndRecord));
         EXEC (@SqlString);
     END;

 

方式四：

/*********************************************************  
* 作    用：數據分頁
* 作    者：Ozawa
* 作者博客：https://www.cnblogs.com/xiongze520/
* 創建日期：2019-05-13
* 使用說明：
　　　　　　[USP_GetPageData] 'select * from 表名',1,10


*********************************************************/





CREATE PROCEDURE [dbo].[USP_GetPageData]
(                         
    @SQLSTR VARCHAR(8000)    -- 查詢的SQL語句 
    , @CURPAGE INT          -- 當前頁面位置
    , @PAGESIZE INT            -- 頁面顯示的數據行數
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE     @P1 INT   --游標
                , @ROWCOUNT INT
                , @COUNTPAGE INT
                , @CurRow INT

    EXEC sp_cursoropen @P1 OUTPUT, @SQLSTR, @scrollopt = 1, @ccopt = 1, @ROWCOUNT = @ROWCOUNT OUTPUT

    IF @ROWCOUNT % @PAGESIZE > 0
        SET @COUNTPAGE = @ROWCOUNT / @PAGESIZE + 1
    ELSE
        SET @COUNTPAGE = @ROWCOUNT / @PAGESIZE

    IF @CURPAGE > @COUNTPAGE
        SET @CURPAGE = @COUNTPAGE

    SET @CurRow = (@CURPAGE - 1) * @PAGESIZE + 1

    SET NOCOUNT OFF
    SELECT @CURPAGE CURPAEG, @PAGESIZE PageSize, @COUNTPAGE COUNTPAGE, @ROWCOUNT [ROWCOUNT]
    EXEC sp_cursorfetch @P1, 16, @CurRow, @PAGESIZE
    SET NOCOUNT ON
    EXEC sp_cursorclose @P1
END



 


還有更多分頁方式，感興趣的可以去查查資料，上面的分頁方式已經足夠參考了，


然後結合自身情況可以寫一個量身定做的分頁方法，後續直接使用就可以了。


 



              
	   




	

	
您的分享是我們最大的動力!




			 	

	
	

    


-Advertisement-
Play Games
     


	


    

    

    




	       	  

              
更多相關文章  

              

                   
    


				
  • 
				
    
			    	
    
				    	搜索引擎框架之ElasticSearch基礎詳解
			    	
    
					
    文章大綱 一、搜索引擎框架基礎介紹二、ElasticSearch的簡介三、ElasticSearch安裝（Windows版本）四、ElasticSearch操作客戶端工具--Kibana五、ES的常用命令六、Java連接ElasticSearch進行數據操作七、項目源碼與參考資料下載八、參考文章 一 ...
    
				
    
		    
  • 
				
    
			    	
    
				    	記安裝Oracle數據遇到的一些問題
			    	
    
					
    最近Oracle資料庫總是出問題，於是卸載乾凈後重新安裝，安裝過程中遇到了一些問題於是百度解決，在這裡記錄下來方便以後查看。 win10系統安裝oracle11g時遇到INS-13001環境不滿足最低要求 在安裝時點擊setup.exe之後，出現了:[INS-13001]環境不滿足最低要求 這時，打 ...
    
				
    
		    
  • 
				
    
			    	
    
				    	大數據-----大數據-機器學習-人工智慧
			    	
    
					
    1.大數據與機器學習的關係： 大數據領域我們做的是數據的存儲和簡單的統計計算，機器學習在大數據的應用是為了發現數據的規律或模型，用機器學習演算法對數據進行計算的到的模型，從而決定我們的預測與決定的因素（比如在大數據用戶畫像項目里，生成的特殊用戶欄位）。 2.大數據在機器學習的應用 目前市場實際開發模式 ...
    
				
    
		    
  • 
				
    
			    	
    
				    	redis的兩種持久化方案
			    	
    
					
    前言 人生在於折騰系列，網路，多線程等系列博客樓主還在繼續折騰也不會放棄。這是全新的系列，緩存的知識其實並不僅僅在於簡單的增刪改查，我覺得有必要全面深入的學習一波。記錄學習的過程與體悟。 RDB 什麼是RDB 對redis中的數據執行周期性的持久化，通過配置文件中設置檢查間隔時間與備份觸發條件來對數 ...
    
				
    
		    
  • 
				
    
			    	
    
				    	Microsoft SQL Server
			    	
    
					
    Microsoft SQL Server SQL Server 是Microsoft 公司推出的關係型資料庫管理系統。具有使用方便可伸縮性好與相關軟體集成程度高等優點，可跨越膝上型電腦到運行Microsoft Windows Server的大型多處理器的伺服器等多種平臺使用。 Microsoft S ...
    
				
    
		    
  • 
				
    
			    	
    
				    	Redis集群設計原理
			    	
    
					
    恢復內容開始 Redis集群設計包括2部分：哈希Slot和節點主從，本篇博文通過3張圖來搞明白Redis的集群設計。 節點主從： 主從設計不算什麼新鮮玩意，在資料庫中我們也經常用主從來做讀寫分離，直接上圖： 圖上能看得到的信息： 1， 只有1個Master，可以有N個slaver，而且Slaver也 ...
    
				
    
		    
  • 
				
    
			    	
    
				    	搜索引擎框架介紹
			    	
    
					
    一、搜索引擎基礎介紹二、常見搜索引擎框架介紹與比較三、參考文章 一、搜索引擎基礎介紹 1. 什麼是搜索引擎 搜索引擎，通常指的是收集了萬維網上幾千萬到幾十億個網頁並對網頁中的每一個詞（即關鍵詞）進行索引，建立索引資料庫的全文搜索引擎。當用戶查找某個關鍵詞的時候，所有在頁面內容中包含了該關鍵詞的網頁都 ...
    
				
    
		    
  • 
				
    
			    	
    
				    	PostgreSQL 務實應用（一/5）樹形層級
			    	
    
					
    項目中，經常會碰到多級的樹形結構數據，如地區信息，省、市、區、街道等，或客戶關係信息上三級，下三級等。 實際項目中，我們可能碰到以下兩種需求： 一條記錄中呈現路徑：省 市 區 街道 一條記錄中呈現上幾級：上級，上上級，上上上級 此情此景，使用 WITH RECURSIVE 遞歸查詢再合適不過。 遞歸 ...
    
				
    
		    
    • 
				 
		  
  
                  

              

          



		
        

        
        

            
 







    
一周排行

    

         
    

 
    -Advertisement-
    Play Games
    	   
	
  • 
			        	
    
			    	        
    
				    	        移動開發（一）：使用.NET MAUI開發第一個安卓APP
			            	
    
				        	
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        wpf ToggleButton選中效果和一個登錄界面
			            	
    
				        	
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        彈幕樹洞項目功能新增篇
			            	
    
				        	
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        第27篇 sqlserver2022詳細安裝步驟
			            	
    
				        	
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        .NET 開源高性能 MQTT 類庫
			            	
    
				        	
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
			            	
    
				        	
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
			            	
    
				        	
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
			            	
    
				        	
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
			            	
    
				        	
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        第28篇 如何.net中實現高效可靠數據同步api
			            	
    
				        	
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
    
			         	
    
		            
    • 										 





		        	
                 	
                 			        

    





    
所有分類