复制代码 代码如下:
–根据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