当前位置：首页 -> 技术开发 -> 数据库 -> sql 存储过程分页

sql 存储过程分页

时间： 2021-07-31 作者：daque

sql 存储过程分页 create proc myx_prpagerecordset@querystr nvarchar(1000), @keyfield nvarchar (200), @pagesize int, @pagenumber int asbegindeclare @sqltext as nvarchar(4000)declare @sqltable as nvarchar(4000)set @sqltable = 'select top ' + cast((@pagenumber + 1) * @pagesize as varchar(30)) + ' ' + @querystrset @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)endgo中心代码dim strsql as stringmycomm = new sqlclient.sqlcommand("myx_prpagerecordset", myconn)mycomm.commandtype = commandtype.storedproceduremycomm.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 = pagesizemycomm.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 fid4 kwklover 25 kwklover 26 kwklover 2按照传入参数,咱们的预期该当是:id name fid1 kwklover 22 kwklover 23 kwklover 2底下是我参照小春的保存分页写的分页保存进程,不妨处置上头的题目:create procedure prgetrecordbypage(@pagesize int, --每页的记载条数 @pagenumber int, --暂时页面@querysql varchar(1000),--局部查问字符串,如* from test order by id desc@keyfield varchar(500) )asbegindeclare @sqltable as varchar(1000)declare @sqltext as varchar(1000)set @sqltable='select top '+cast(@pagenumber*@pagesize as varchar(30))+' '+@querysqlset @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)endgo

sql 存储过程分页

相关推荐

推荐下载

热门阅览

最新排行