存储过程分页的经典例子

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--EXEC [dbo].[CP_SELECT_SPLIT_PAGE] 'STOCK_ID',100,7

-- ===========存储过程分页的经典例子==================================
-- @PrimaryKey  用于定位记录的主键(惟一键)字段,只能是单个字段
--  @CurrentPage 要显示的页码
--  @PageSize    每页的大小(记录数)
-- ===================================================================
ALTER PROCEDURE [dbo].[CP_SELECT_SPLIT_PAGE]
 @PrimaryKey VARCHAR(100) ,
  @CurrentPage INT=1,
  @PageSize INT=10   
AS
  BEGIN 
 
    WITH  NEW_SPLIT_PAGE_TABLE
            AS (SELECT ROW_NUMBER() OVER (ORDER BY @PrimaryKey) AS ROWNUMBER , *
                  FROM PRD_JO_WIP_HD)
      SELECT * FROM NEW_SPLIT_PAGE_TABLE
        WHERE RowNumber >= (@CurrentPage - 1) * @PageSize + 1 AND RowNumber <= (@CurrentPage - 1) * @PageSize + (@PageSize)

    SELECT COUNT(*) FROM PRD_JO_WIP_HD
   
  END