使用存储过程实现分页查询,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 参数,来指示需要往前推进几条数据,这个参数就是你在请求之前删除的条数