澳门新浦金网站两款sql 分页存储过程代码

2020-01-11 12:13 来源:未知

复制代码 代码如下: USE NBDXMIS CREATE proc TestTimeAnySentence @sql_where varchar(8000) as declare @ct datetime set @ct = getdate() declare @newsql_where varchar(8000) set @newsql_where=@sql_where exec(@newsql_where) select datediff(ms, @ct ,getdate()) as '查询时间' go TestTimeAnySentence 'select * from 水费表 where 费用ID200000'

文章收藏了两款sql 分页存储过程代码,这二款存储过程是二款高效分页存储过程代码,如果你觉得自己写的语句不够,强的话,可以利用我们现成的高效分页存储过程实例代码。

文章收藏了两款sql 分页存储过程代码,这二款存储过程是二款高效分页存储过程代码,如果你觉得自己写的语句不够,强的话,可以利用我们现成的高效分页存储过程实例代码。

create procedure pages@tablenames varchar(200), --表名,可以是多个表,但不能用别名@primarykey varchar(100), --主键,可以为空,但@order为空时该值不能为空@fields varchar(800), --要取出的字段,可以是多个表的字段,可以为空,为空表示select *@pagesize int, --每页记录数@currentpage int, --当前页,0表示第1页@filter varchar(200) = '', --条件,可以为空,不用填 where@order varchar(200) = '' --排序,可以为空,为空默认按主键升序排列,不用填 order byasbegindeclare @toprow varchar(12)declare @temppagesize varchar(12)if(len(@order)0)beginset @order=' order by '+@orderendelsebeginset @order=''endif (len(@filter)1)beginset @filter=' 1=1'endif(@currentpage-1=0)set @currentpage=0set @toprow= rtrim(ltrim(str(@pagesize*(@currentpage-1))))set @temppagesize= rtrim(ltrim(str(@pagesize)))exec('declare @temptable table(rownum int identity(1,1),gid varchar(12))'+'declare @datatable table(gid varchar(12))'+'declare @timer datetime'+'set @timer = getdate() '+'set nocount on '+'insert into @temptable(gid) select '+@primarykey+' from '+@tablenames+' where '+@filter+@order+'insert into @datatable(gid) select top '+@temppagesize+'gid from @temptable where rownum'+@toprow+'set rowcount '+@temppagesize+'

select '+@fields+' from '+@tablenames+' where '+@filter+' and '+@primarykey+' in (select gid from @datatable)'+@order+'set rowcount 0'+'print(datediff(millisecond, @timer, getdate()))')print('insert into @temptable(gid) select '+@primarykey+' from '+@tablenames+' where '+@filter+@order)print('select '+@fields+' from '+@tablenames+' where '+@filter+' and '+@primarykey+' in(select gid from @datatable) '+@order) endgo

TAG标签:
版权声明:本文由澳门新浦金网站发布于数据库,转载请注明出处:澳门新浦金网站两款sql 分页存储过程代码