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实现分页查询效率更高 ，
