Sql Server 存儲過程分頁
- 2019 年 10 月 4 日
- 筆記
在企業級項目開發中，分頁查詢，獲取某一類數據的List列表，這一功能是最普遍也是最重要的功能。其做法有很多種，例如ORM中自定義分頁查詢,一般情況下是拼接強類型的查詢條件，然後轉換成sql語句，查出出分頁結果。在ORM轉換過程中會稍微損失性能，效率會降低。對於百萬級以上的大數據量，要求查詢介面顯示速度快，此時手動寫存儲過程，並且在存儲過程中分頁是最佳選擇。下面給出具體的示例與說明：
============================================= -- Author: XXX -- Create date: XXX -- Description: XXX -- ============================================= ALTER PROCEDURE [dbo].[SP_GetRptNoCooperation] @custId NVARCHAR(30) --客戶編號 ,@custNam NVARCHAR(100) --客戶名稱 ,@stopWorkingDateStart DATETIME -- 停止合作日期_起 ,@stopWorkingDateEnd DATETIME -- 停止合作日期_止 ,@crtDtStart DATETIME -- 申報日期_起 ,@crtDtEnd DATETIME -- 申報日期_止 ,@pageSize INT --單頁記錄條數 ,@pageIndex INT --當前頁左索引 ,@totalRowCount INT OUTPUT --輸出總記錄條數 AS BEGIN DECLARE @RowStart INT; --定義分頁起始位置 DECLARE @RowEnd INT; --定義分頁結束位置 DECLARE @Sql NVARCHAR(MAX); --拼接SQL語句 DECLARE @SqlSelectResult NVARCHAR(MAX); --Sql查詢結果語句 -- DECLARE @SqlCount NVARCHAR(MAX); --Sql Count計數語句 IF @pageIndex > 0 BEGIN SET @pageIndex = @pageIndex -1; SET @RowStart = @pageSize * @pageIndex + 1; SET @RowEnd = @RowStart + @pageSize - 1; END ELSE BEGIN SET @RowStart = 1; SET @RowEnd = 999999; END IF ISNULL(@pageSize, 0) <> 0 AND @pageSize <> 0 BEGIN SET @sql = 'With CTE_RptNoCooperation as ( SELECT ROW_NUMBER () OVER (ORDER BY rnc.CrtDt DESC) AS RowNumber ,rnc.Id ,rnc.CustId --客戶編號 ,rnc.StopWorkingDate --停止合作日期（最後一次發貨日期） ,rnc.Arrears --截止申報日期的總欠 ,rnc.CheckAccount --對賬情況（是否對清、對至幾月份） ,rnc.Communication --前期溝通處理情況（是否有退貨/業務溝通催款情況/報法務室/出律師函或公函等） ,rnc.MaySituation --XXXX年X月跟蹤情況 ,rnc.JuneManagerSuggest --XXXX年X月部區經理意見 ,rnc.JunefinancialOpinion --財務審計部意見 ,rnc.CEOInstruct --總裁批示 ,rnc.CrtDt --創建日期 ,rnc.CrtBy --創建人id ,rnc.UpdateDt --修改日期 ,rnc.UpdateBy --修改人id ,ci.CustNam --客戶名稱 ,ai2.AreaNam --區域名稱，省份 FROM RptNoCooperation AS rnc LEFT JOIN CustInfo AS ci ON rnc.CustId = ci.CustId LEFT JOIN AreaInfo AS ai ON ci.AreaCode = ai.AreaCode INNER JOIN AreaInfo AS ai2 ON ai.PareaCode = ai2.AreaCode WHERE 1 = 1 ';--此處CTE表達式右括弧不寫，在後面根據條件判斷，追加 END ELSE BEGIN SET @sql = 'SELECT rnc.Id ,rnc.CustId --客戶編號 ,rnc.StopWorkingDate --停止合作日期（最後一次發貨日期） ,rnc.Arrears --截止申報日期的總欠 ,rnc.CheckAccount --對賬情況（是否對清、對至幾月份） ,rnc.Communication --前期溝通處理情況（是否有退貨/業務溝通催款情況/報法務室/出律師函或公函等） ,rnc.MaySituation --XXXX年X月跟蹤情況 ,rnc.JuneManagerSuggest --XXXX年X月部區經理意見 ,rnc.JunefinancialOpinion --財務審計部意見 ,rnc.CEOInstruct --總裁批示 ,rnc.CrtDt --創建日期 ,rnc.CrtBy --創建人id ,rnc.UpdateDt --修改日期 ,rnc.UpdateBy --修改人id ,ci.CustNam --客戶名稱 ,ai2.AreaNam --區域名稱，省份 FROM RptNoCooperation AS rnc LEFT JOIN CustInfo AS ci ON rnc.CustId = ci.CustId LEFT JOIN AreaInfo AS ai ON ci.AreaCode = ai.AreaCode INNER JOIN AreaInfo AS ai2 ON ai.PareaCode = ai2.AreaCode WHERE 1 = 1 '; END IF ISNULL(@custId,'') <> '' BEGIN --根據客戶id查詢 SET @Sql = @Sql + ' AND rnc.CustId = ''' + @custId + ''''; END IF ISNULL(@custNam,'') <> '' BEGIN --根據客戶名稱 模糊查詢 SET @Sql = @Sql + ' AND ci.CustNam like ''%' + @custNam + '%'''; END IF ISNULL(@stopWorkingDateStart,'') <> '' BEGIN --停止合作日期_起 SET @stopWorkingDateStart = @stopWorkingDateStart + ' 00:00:00.000'; SET @Sql = @Sql + ' AND rnc.StopWorkingDate >= ''' + @stopWorkingDateStart + ''''; END IF ISNULL(@stopWorkingDateEnd,'') <> '' BEGIN --停止合作日期_止 SET @stopWorkingDateEnd = @stopWorkingDateEnd + ' 23:59:59.999' SET @Sql = @Sql + ' AND rnc.StopWorkingDate <= ''' + @stopWorkingDateEnd + ''''; END IF ISNULL(@crtDtStart,'') <> '' BEGIN --申請日期_起 SET @crtDtStart = @crtDtStart + ' 00:00:00.000'; SET @Sql = @Sql + ' AND rnc.CrtDt >= ''' + @crtDtStart + ''''; END IF ISNULL(@crtDtEnd,'') <> '' BEGIN --申請日期_止 SET @crtDtEnd = @crtDtEnd + ' 23:59:59.999' SET @Sql = @Sql + ' AND rnc.CrtDt <= ''' + @crtDtEnd + ''''; END IF ISNULL(@pageSize, 0) <> 0 AND @pageSize <> 0 BEGIN SET @Sql = @Sql + ') '; SET @SqlCount = @Sql + ' SELECT @Temp = COUNT(*) FROM CTE_RptNoCooperation;'; SET @SqlSelectResult = @Sql + ' SELECT * FROM CTE_RptNoCooperation WHERE RowNumber Between ' + Convert(varchar(10),@RowStart) + ' And ' + Convert(varchar(10),@RowEnd) + ';'; --Print (@SqlSelectResult); --EXEC (@SqlSelectResult); EXEC sp_executesql @SqlSelectResult; EXEC sp_executesql @SqlCount,N'@Temp int output',@totalRowCount output ; END ELSE BEGIN SET @Sql = @sql + ' order by rnc.CrtDt DESC '; SET @totalRowCount = 0; --Print (@Sql); EXEC (@Sql); END END GO
SQL Server 2012 及以上版本使用OFFSET/FETCH NEXT實現分頁查詢效率更高 ，
