三种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

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐