三种SQL分页查询的存储过程代码

--根据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