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)=&＃39;&＃39;, --查询条件 
    @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&＃39;select @sCount=count(1) FROM &＃39;+@SqlTable+&＃39; WHERE 1=1 &＃39;+@SqlWhere
    Exec sp_executesql @sqlstr,N&＃39;@sCount int outPut&＃39;,@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&＃39;insert into #pageindex(nid) select &＃39;+@SqlPK+&＃39; from &＃39;+@SqlTable+&＃39; where 1=1 &＃39;+@SqlWhere+&＃39; &＃39;+@SqlOrder

Exec sp_executesql @sqlstr
SET @sqlstr=&＃39;select &＃39;+@SqlField+&＃39; FROM &＃39;+ @SqlTable +&＃39; inner join #pageindex p on &＃39;+@SqlPK+&＃39;=p.nid and (p.id>&＃39;+STR(@PageLowerBound)+&＃39;) and (p.id<=&＃39;+STR(@PageUpperBound)+&＃39;)&＃39; +&＃39; &＃39;+@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)=&＃39;&＃39;, --查询条件 
    @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&＃39;select @sCount=count(1) FROM &＃39;+@SqlTable+&＃39; WHERE 1=1 &＃39;+@SqlWhere
    Exec sp_executesql @sqlstr,N&＃39;@sCount int outPut&＃39;,@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&＃39;insert into #pageindex(nid) select &＃39;+@SqlPK+&＃39; from &＃39;+@SqlTable+&＃39; where 1=1 &＃39;+@SqlWhere+&＃39; &＃39;+@SqlOrder

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

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

添加了一个 Skip 参数,来指示需要往前推进几条数据,这个参数就是你在请求之前删除的条数

以上就是SQLsever存储过程分页查询的代码示例的详细内容，更多请关注 第一PHP社区 其它相关文章！