The content of this article is about the code example of SQL server stored procedure paging query. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.
Use stored procedures to implement paging queries. The SQL statement is as follows:
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
But if you have some strange needs, such as not returning to the first page after deleting the current page data, then Continue to request the next page. At this time, there will be a query where the data has been skipped.
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
Added a Skip parameter to indicate how much to advance. pieces of data, this parameter is the number of pieces you deleted before requesting
The above is the detailed content of Code example of SQLserver stored procedure paging query. For more information, please follow other related articles on the PHP Chinese website!