通用分页存储过程

/*

     通用分页(单表与多表 多字段排序)

*/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[comm_Paging]') AND type in (N'P', N'PC'))
DROP PROCEDURE comm_Paging
Go
create procedure comm_Paging
(
@table nvarchar(300),--表名
@pageSize int=10,--页大小
@pageIndex int=1,--第几页
@field nvarchar(300)='*',--显示的字段
@key nvarchar(20)='',--排序的字段
@orderfield nvarchar(50)='',--多字段排序
@ordertype bit=1,--排序方向 1倒序 0顺序
@strWhere nvarchar(300)=''--where 语句
)
as
begin

    declare @str nvarchar(1000)
    if(@pageIndex>1)
        begin
            if(@ordertype=1)
               begin
                set @str = ' select top '+convert(nvarchar,@pageSize)+' '+@field+' from '+@table
                set @str +=' where '+@key+'<(select ISNULL(min('+substring(@key,CHARINDEX('.',@key)+1,LEN(@key))+'),0) from'
                set @str +=' (select top '+ convert(nvarchar,((@pageIndex-1)*@pageSize)) +' '+@key+' from '+@table+' where 1=1 '+@strWhere+' ORDER BY '+@key+' desc)'
                set @str +=' as id ) '+@strWhere+' ORDER BY '+@key+' desc '+@orderfield
               end
               else
               begin
                set @str = ' select top '+convert(nvarchar,@pageSize)+' '+@field+' from '+@table
                set @str +=' where '+@key+'>(select ISNULL(max('+substring(@key,CHARINDEX('.',@key)+1,LEN(@key))+'),0) from'
                set @str +=' (select top '+ convert(nvarchar,((@pageIndex-1)*@pageSize)) +' '+@key+' from '+@table+' where 1=1 '+@strWhere+' ORDER BY '+@key+' asc)'
                set @str +=' as id ) '+@strWhere+' ORDER BY '+@key+' asc '+@orderfield
               end
        end
    else
        begin
               set @str =' select top '+convert(nvarchar,@pageSize)+' '+@field+' from '+@table
               set @str+=' where 1=1 '+@strWhere+' ORDER BY '+@key
               if(@ordertype=1)
               begin
               set @str+=' desc '+@orderfield
               end
               else
               begin
               set @str+=' asc '+@orderfield
               end
        end
        print(@str)
    exec sp_executesql @str
    
end
GO

--DEMO例子

drop table a
go
create table a
(
id int identity(1,1),
name nvarchar(50)
)
go
drop table b
go
create table b
(
id int identity(1,1),
aid int,
name nvarchar(50)
)
go

declare @count int=0
declare @temp int
while(1=1)
begin

      if(@count>=100)
      begin
            break
      end
      else
      begin
            set @count+=1
           insert into a(name) 
           select '张三'+CONVERT(nvarchar,@count,20) 
           set @temp=@@IDENTITY
           insert into b(aid,name) 
           select @temp,'李四'+CONVERT(nvarchar,@count,20)
      end
end
go

select *from a 

select * from b

select * from a inner join b
on(a.id=b.aid)
go
----多表排序
comm_Paging 'a inner join b on (a.id=b.aid)',10,1,'*','a.id',',b.id asc',1,''
go
----单表排序
comm_Paging 'a',10,1,'*','id',',name asc',1,''
go

 

此存储过程只针对按顺序增长的唯一列作为key来分页，不支持guid.

 

转载于:https://www.cnblogs.com/haomo/p/sql.html