运用存储历程完成分页查询,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 参数,来指导须要往前推动几条数据,这个参数就是你在要求之前删除的条数
以上就是SQLsever存储历程分页查询的代码示例的细致内容,更多请关注ki4网别的相干文章!