SQLsever存儲過程分頁查詢

使用存儲過程實現分頁查詢,SQL語句如下:

USE [DatebaseName]  --資料庫名
GO
/****** Object:  StoredProcedure [dbo].[Pagination]    Script Date: 03/30/2019 10:36:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[Pagination]
(
    @SqlTable varchar(1000),--要查詢的表或視圖,也可以一句sql語句
    @SqlPK varchar(50),--主鍵
    @SqlField varchar(1000),--查詢的欄位
    @SqlWhere varchar(1000)='', --查詢條件 
    @SqlOrder varchar(200),--排序
    @PageSize int=20,--每頁的記錄數
    @PageIndex int=1, --第幾頁,預設第一頁
    @IsCount bit, --是否獲取記錄數
    @RecordCount int=0 output
)
AS
SET NOCOUNT ON
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @sqlstr nvarchar(2000)

--獲取記錄數
IF @IsCount=1
BEGIN
    SET @sqlstr=N'select @sCount=count(1) FROM '+@SqlTable+' WHERE 1=1 '+@SqlWhere
    Exec sp_executesql @sqlstr,N'@sCount int outPut',@RecordCount OUTPUT
END

SET @PageLowerBound=(@PageIndex-1)*@PageSize
SET @PageUpperBound=@PageLowerBound+@PageSize
CREATE TABLE #pageindex(id int identity(1,1) not null,nid varchar(100))
SET rowcount @PageUpperBound 
SET @sqlstr=N'insert into #pageindex(nid) select '+@SqlPK+' from '+@SqlTable+' where 1=1 '+@SqlWhere+' '+@SqlOrder

Exec sp_executesql @sqlstr
SET @sqlstr='select '+@SqlField+' FROM '+ @SqlTable +' inner join #pageindex p on '+@SqlPK+'=p.nid and (p.id>'+STR(@PageLowerBound)+') and (p.id<='+STR(@PageUpperBound)+')' +' '+@SqlOrder

Exec sp_executesql @sqlstr
SET NOCOUNT OFF
DROP TABLE #pageindex

但是如果你有一些奇怪的需求,比如刪除當前頁數據之後不重新返回第一頁,然後繼續請求下一頁,這時會出現有一下數據被跳過查詢

解決方案如下:

USE [DatebaseName]
GO

/****** Object:  StoredProcedure [dbo].[Pagination]    Script Date: 03/30/2019 14:41:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[PaginationSkip]
(
    @SqlTable varchar(1000),--要查詢的表或視圖,也可以一句sql語句
    @SqlPK varchar(50),--主鍵
    @SqlField varchar(1000),--查詢的欄位
    @SqlWhere varchar(1000)='', --查詢條件 
    @SqlOrder varchar(200),--排序
    @PageSize int=20,--每頁的記錄數
    @PageIndex int=1, --第幾頁,預設第一頁
    @IsCount bit, --是否獲取記錄數
    @RecordCount int=0 output,
    @Skip int=0 --跳過記錄數
)
AS
SET NOCOUNT ON
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @sqlstr nvarchar(2000)

--獲取記錄數
IF @IsCount=1
BEGIN
    SET @sqlstr=N'select @sCount=count(1) FROM '+@SqlTable+' WHERE 1=1 '+@SqlWhere
    Exec sp_executesql @sqlstr,N'@sCount int outPut',@RecordCount OUTPUT
END

SET @PageLowerBound=(@PageIndex-1)*@PageSize-@Skip  --減去刪除的條數,以適應需求
SET @PageUpperBound=@PageLowerBound+@PageSize-@Skip 
CREATE TABLE #pageindex(id int identity(1,1) not null,nid varchar(100))
SET rowcount @PageUpperBound 
SET @sqlstr=N'insert into #pageindex(nid) select '+@SqlPK+' from '+@SqlTable+' where 1=1 '+@SqlWhere+' '+@SqlOrder

Exec sp_executesql @sqlstr
SET @sqlstr='select '+@SqlField+' FROM '+ @SqlTable +' inner join #pageindex p on '+@SqlPK+'=p.nid and (p.id>'+STR(@PageLowerBound)+') and (p.id<='+STR(@PageUpperBound)+')' +' '+@SqlOrder

Exec sp_executesql @sqlstr
SET NOCOUNT OFF
DROP TABLE #pageindex
GO

添加了一個 Skip 參數,來指示需要往前推進幾條數據,這個參數就是你在請求之前刪除的條數