SqlServer 分页存储过程

关注

原创最新推荐文章于 2026-03-23 00:23:54 发布 1.1k 阅读 AI 写同款· GEO 优化 ›

[MSSQL]mssql海量高效分页存储过程 CREATE PROCEDURE [dbo].[PROC_GetListRecordByPage] @tblName VARCHAR(255), -- 表名 @fldName VARCHAR(255), -- 主键字段名 @PageSize INT = 10,-- 页尺寸 @PageIndex INT = 1, -- 页码 @IsReCount INT = 0, -- 0 返回记录总数; 阅读详情

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go





-- =============================================
-- Author:		<许刚>
-- Create date: <2011/9/22>
-- Description:	<通用存储过程>
-- =============================================
ALTER PROC [dbo].[sp_CommonPageSplit]
      @curPage INT , --当前页
      @pageSize INT , --没页数据数量
      @tbName VARCHAR(1000) ,--分页的表名
      @columNames VARCHAR(1000) ,--需要查询字段
      @orderBy VARCHAR(200) ,--排序方式
      @condition VARCHAR(2000) ,--查询条件
      @totalPage INT = 0 OUTPUT ,
      @totalCount INT = 0 OUTPUT
AS 
      BEGIN
            DECLARE @curPageData INT
            DECLARE @sqlStr VARCHAR(6000)
            DECLARE @sqlCountStr NVARCHAR(4000)
            BEGIN
                  IF @condition != '' 
                     SET @condition = ' where ' + @condition
                  IF @orderBy != '' 
                     SET @orderBy = ' order by ' + @orderBy
                  PRINT @condition
                  SET @sqlCountStr = 'select @totalCount=count(1) from  ' + @tbName + @condition
                  EXEC sp_executesql @sqlCountStr, N'@totalCount int output', @totalCount OUT
                  IF @pageSize < 1 
                     BEGIN
                           SET @pageSize = 1
                     END
                  IF @totalCount % @pageSize = 0 
                     BEGIN
                           SET @totalPage = @totalCount / @pageSize
                     END
                  ELSE 
                     BEGIN
                           SET @totalPage = @totalCount / @pageSize + 1
                     END
                  IF @curPage > @totalPage 
                     BEGIN
                           SET @curPage = @totalPage
                     END
                  IF @curPage < 1 
                     BEGIN
                           SET @curPage = 1
                     END
                  SET @curPageData = @totalCount - ( @curPage - 1 ) * @pageSize
                  SET @sqlStr = '   With SQLPaging  
			As  
			(  
			Select Top(' + CAST(@pageSize * @curPage AS VARCHAR(60)) + ') ROW_NUMBER() OVER (' + @orderBy
                      + ') as resultNum, ' + @columNames + '  
				FROM ' + @tbName + @condition + @orderBy + '  
			)  
			select * from SQLPaging where resultNum > ' + CAST(( @curPage - 1 ) * @pageSize AS VARCHAR(60)) 
                  PRINT @sqlStr
                  EXECUTE(@sqlStr)
            END
      END

收录于