create proc myx_prpagerecordset
@querystr nvarchar(1000),
@keyfield nvarchar (200),
@pagesize int,
@pagenumber int
as
begin
declare @sqltext as nvarchar(4000)
declare @sqltable as nvarchar(4000)
set @sqltable = 'select top ' + cast((@pagenumber + 1) * @pagesize as varchar(30)) + ' ' + @querystr
set @sqltext =
'select top ' + cast(@pagesize as varchar(30)) + ' * ' +
'from (' + @sqltable + ') as tablea ' +
'where ' + @keyfield + ' not in(select top ' +
cast(@pagenumber * @pagesize as varchar(30)) + ' ' + @keyfield +
' from (' + @sqltable + ') as tableb)'
exec (@sqltext)
end
go
核心代码
dim strsql as string
mycomm = new sqlclient.sqlcommand("myx_prpagerecordset", myconn)
mycomm.commandtype = commandtype.storedprocedure
mycomm.parameters.add(new sqlclient.sqlparameter("@querystr", sqldbtype.nvarchar, 1000))
mycomm.parameters("@querystr").value = " * from tbpage order by id desc"
mycomm.parameters.add(new sqlclient.sqlparameter("@keyfield", sqldbtype.nvarchar, 200))
mycomm.parameters("@keyfield").value = "[id]"
mycomm.parameters.add(new sqlclient.sqlparameter("@pagesize", sqldbtype.nvarchar, 1000))
mycomm.parameters("@pagesize").value = pagesize
mycomm.parameters.add(new sqlclient.sqlparameter("@pagenumber", sqldbtype.nvarchar, 1000))
mycomm.parameters("@pagenumber").value = mypage - 1
呵呵,执行几w条的代码只需150毫秒左右
建立一个test(id,name,fid)
向test添充几十条数据,使id=1,2,3,4.........(即递增的integer),其他任意
在t-sql debugger给改存储过程分别传递如下参数:
@querystr= * from test
@keyfield=[id]
@pagesize=3
@pagenumber=1
问题出来了,看输出结果(注意id):
id name fid
4 kwklover 2
5 kwklover 2
6 kwklover 2
根据传入参数,我们的预期应该是:
id name fid
1 kwklover 2
2 kwklover 2
3 kwklover 2
下面是我参照小春的存储分页写的分页存储过程,可以解决上面的问题:
create procedure prgetrecordbypage
(
@pagesize int, --每页的记录条数
@pagenumber int, --当前页面
@querysql varchar(1000),--部分查询字符串,如* from test order by id desc
@keyfield varchar(500)
)
as
begin
declare @sqltable as varchar(1000)
declare @sqltext as varchar(1000)
set @sqltable='select top '+cast(@pagenumber*@pagesize as varchar(30))+' '+@querysql
set @sqltext='select top '+cast(@pagesize as varchar(30))+' * from '
+'('+@sqltable+') as tembtba '
+'where '+@keyfield+' not in (select top '+cast((@pagenumber-1)*@pagesize as varchar(30))+' '+@keyfield+' from '
+'('+@sqltable+') as temptbb)'
exec(@sqltext)
end
go
如对本文有疑问，
点击进行留言回复！！
我要评论
网友评论