mmssqlserver中三种分页存储过程代码

作者：袖梨 2022-06-29

--根据MAX(MIN)ID

 代码如下 复制代码
CREATE PROC [dbo].[proc_select_id]
@pageindex int=1,--当前页数
@pagesize int=10,--每页大小
@tablename VARCHAR(50)='',--表名
@fields VARCHAR(1000)='',--查询的字段集合
@keyid VARCHAR(50)='',--主键
@condition NVARCHAR(1000)='',--查询条件
@orderstr VARCHAR(500),--排序条件
@totalRecord BIGINT OUTPUT--总记录数
AS
 IF ISNULL(@orderstr,N'')=N'' SET @orderstr=N' ORDER BY '+@keyid+N' DESC '
 IF ISNULL(@fields,N'')=N'' SET @fields=N'*'
 IF ISNULL(@condition,N'')=N'' SET @condition=N'1=1'
 DECLARE @sql NVARCHAR(4000)
 --IF(@totalRecord IS NULL)
 --BEGIN
  SET @sql=N'SELECT @totalRecord=COUNT(*)'
   +N' FROM '+@tablename
   +N' WHERE '+@condition
  EXEC sp_executesql @sql,N'@totalRecord INT OUTPUT',@totalRecord OUTPUT
 --END
 IF(@pageindex=1)
 BEGIN
  SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@condition+N' '+@orderstr
  EXEC(@sql)
 END
 ELSE
 BEGIN
  DECLARE @operatestr CHAR(3),@comparestr CHAR(1)
  SET @operatestr='MAX'
  SET @comparestr='>'
  IF(@orderstr<>'')
  BEGIN
   IF(CHARINDEX('desc',LOWER(@orderstr))<>0)
   BEGIN
    SET @operatestr='MIN'
    SET @comparestr='<'
   END
  END
  SET @sql=N'SELECT top '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@keyid+@comparestr
   +N'(SELECT '+@operatestr+N'('+@keyid+N') FROM '+@tablename+N' WHERE '+@keyid
   +N' IN (SELECT TOP '+STR((@pageindex-1)*@pagesize)+N' '+@keyid+N' FROM '+@tablename+N' WHERE '
   +@condition+N' '+@orderstr+N')) AND '+@condition+N' '+@orderstr
  EXEC(@sql)
 END
GO


--根据ROW_NUMBER() OVER

 代码如下 复制代码
CREATE PROC [dbo].[proc_select_page_row]
@pageindex INT=1,--当前页数
@pagesize INT=10,--每页大小
@tablename VARCHAR(50)='',--表名
@fields VARCHAR(1000)='*',--查询的字段集合
@keyid VARCHAR(50)='',--主键
@condition NVARCHAR(1000)='',--查询条件
@orderstr VARCHAR(500),--排序条件
@totalRecord BIGINT  OUTPUT--总记录数
AS
 IF ISNULL(@orderstr,N'')=N'' SET @orderstr=N' ORDER BY '+@keyid+N' DESC '
 IF ISNULL(@fields,N'')=N'' SET @fields=N'*'
 IF ISNULL(@condition,N'')=N'' SET @condition=N'1=1'
 DECLARE @sql NVARCHAR(4000)
-- IF @totalRecord IS NULL
-- BEGIN
  SET @sql=N'SELECT @totalRecord=COUNT(*)'
   +N' FROM '+@tablename
   +N' WHERE '+@condition
  EXEC sp_executesql @sql,N'@totalRecord bigint OUTPUT',@totalRecord OUTPUT
--END
 IF(@pageindex=1)
 BEGIN
  SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@condition+N' '+@orderstr
  EXEC(@sql)
 END
 ELSE
 BEGIN
  DECLARE @StartRecord INT
  SET @StartRecord = (@pageindex-1)*@pagesize + 1
  SET @sql=N'SELECT * FROM (SELECT ROW_NUMBER() OVER ('+ @orderstr +N') AS rowId,'+@fields+N' FROM '+ @tablename+N') AS T WHERE rowId>='+STR(@StartRecord)+N' and rowId<='+STR(@StartRecord + @pagesize - 1)
  EXEC(@sql)
 END
GO


--根据TOP ID

 代码如下 复制代码
CREATE PROC [dbo].[proc_select_page_top]
@pageindex INT=1,--当前页数
@pagesize INT=10,--每页大小
@tablename VARCHAR(50)='',--表名
@fields VARCHAR(1000)='',--查询的字段集合
@keyid VARCHAR(50)='',--主键
@condition NVARCHAR(1000)='',--查询条件
@orderstr VARCHAR(500),--排序条件
@totalRecord BIGINT OUTPUT--总记录数
AS
 IF ISNULL(@orderstr,N'')=N'' SET @orderstr=N' ORDER BY '+@keyid+N' DESC '
 IF ISNULL(@fields,N'')=N'' SET @fields=N'*'
 IF ISNULL(@condition,N'')=N'' SET @condition=N'1=1'
 DECLARE @sql NVARCHAR(4000)
 --IF(@totalRecord IS NULL)
 --BEGIN
  SET @sql=N'SELECT @totalRecord=COUNT(*)'
   +N' FROM '+@tablename
   +N' WHERE '+@condition
  EXEC sp_executesql @sql,N'@totalRecord INT OUTPUT',@totalRecord OUTPUT
 --END
 IF(@pageindex=1)
 BEGIN
  SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@condition+N' '+@orderstr
  EXEC(@sql)
 END
 ELSE
 BEGIN
  SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE  '+@keyid
  +N' NOT IN(SELECT TOP '+STR((@pageindex-1)*@pagesize)+N' '+@keyid+N' FROM '
  +@tablename+N' WHERE '+@condition+N' '+@orderstr+N') AND '+@condition+N' '+@orderstr
  EXEC(@sql)
 END
GO

相关文章

精彩推荐