SQL server分页代码

分页功能是每个程序员必备的技能。

第一种方法是ROW_NUMBER的方法。这个特点是每次分页，都写一大篇分页代码的存储过程。

还需要用到临时表等。

分页的存储过程：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Liu>
-- Create date: <Create Date,,2019-12-23>
-- Description:    <Description,,User表分页>
-- =============================================
CREATE PROCEDURE UserPageList
@CurrentPage int,
@PageSize int
AS
BEGIN
    SET NOCOUNT ON;
    declare
    @SQL nvarchar(max);
    set @SQL=' with Temp as(select ROW_NUMBER() over(Order by UserId) as RowIndex,UserId,UserName from S_User where 1=1)select Temp.* 
    from Temp where Temp.RowIndex between (@CurrentPage - 1) * @PageSize and @CurrentPage * @PageSize order by Temp.RowIndex';
    

END
GO

Temp 临时表会在调用完之后销毁。#临时表 需要单独执行drop删表。

第二种方法：OFFSET/FETCH NEXT来实现分页，在sql2012中加入的分页方法。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Liu>
-- Create date: <Create Date,,2019-12-23>
-- Description:    <Description,,User表分页>
-- =============================================
CREATE PROCEDURE UserPageList
@CurrentPage int,
@PageSize int
AS
BEGIN
    SET NOCOUNT ON;
    declare
    @SQL nvarchar(max);
    set @SQL='select UserId,UserName from S_USer order by UserId OFFSET @PageSize*(@CurretnPage-1) ROWS FETCH NEXT @PageSize ROWS ONLY';
    

END
GO

 新特性的语句就像 ModelList.Skip(num).Take(num2);

SQL server 2012 T-SQL 新特性