sybase分页存储过程的实现

来源：互联网发布：抓包怎么看数据信息编辑：程序博客网时间：2024/05/21 23:01

sybase分页存储过程应该如何实现呢？这是很多人都提到的问题，下面就为您介绍sybase分页存储过程的写法，希望可以让您对sybase分页存储过程有更多的了解。

create procedure SP_PHP_PAGE @qry varchar(16384),@iStart int, @iLimit int, @sKeyFiled  varchar(32) as   /*@qry SQL语句, @iStart 开始, @iLimit 结束,@sKeyFiled 表中的主键 */  begin  declare @execsql varchar(16384)  declare @execsqltmp varchar(16384)  /*定义临时表表名*/  declare @dt varchar(10) --生成临时表的随机数  set @dt=substring(convert(varchar, rand()), 3, 10)   --一个字符型的随机数  set rowcount @iLimit      if(@sKeyFiled is null)  begin      set @execsql = stuff(@qry,charindex('select',@qry),6,'select number(*) as sybid,')             set @execsqltmp = ' select * from #temptable' + @dt + ' where sybid>' || convert(varchar,@iStart) || ' and sybid <= ' || convert(varchar,(@iStart/@iLimit+1)*@iLimit)     end  else  begin  set @execsql = stuff(@qry,charindex('select',@qry),6,'select number(*) as sybid,' || @sKeyFiled || ' ,@' )            set @execsql =  stuff(@execsql,charindex(',@',@execsql),charindex('from',@execsql)-charindex(',@',@execsql),'' )            set @execsqltmp = ' select '|| @sKeyFiled ||' from #temptable' + @dt + ' where sybid>' || convert(varchar,@iStart) || ' and sybid <= ' || convert(varchar,(@iStart/@iLimit+1)*@iLimit)    set @execsqltmp = stuff(@qry,charindex('where',@qry),5,' where '|| @sKeyFiled || ' in ('|| @execsqltmp ||') and ')     end  set @execsql = stuff(@execsql, charindex('from',@execsql),4,'into #temptable' + @dt + ' from')  select (@execsql) as sql, @execsqltmp as sqlTmp  set rowcount 0  end

调用

$sSQL = " exec SP_PHP_PAGE '$sSQL',$iStart,$iLimit,'iId'";  $pRow = $this->m_hDb->GetResult ( $sSQL );  $this->m_hDb->Excute ( $pRow->sql );  $pData = $this->m_hDb->Select($pRow->sqlTmp);

Reference：

http://database.51cto.com/art/201011/235484.htm

0 0